SQLite Home page http://www.sqlite.org/

Wikipedia http://de.wikipedia.org/wiki/SQLite

Dokumentation http://www.sqlite.org/docs.html

Tutorials http://www.tutorialspoint.com/sqlite

 sudo apt-get install sqlite3

table Log (
  Id       integer primary key autoincrement,
  DateI    integer, # Insert timestamp unix time
  Msg      text,    # Log message text
  LogLevel text,    # level of importance critical, important, info
  value    real     # value if relevant for mlog message
          )
  
table Sensor (
  Id          integer primary key autoincrement,
  DateI       integer, # Insert timestamp unix time
  Sensor      text,    # Sensor ID
  Sonsortyp   text,    # Sensor type
  Room        text     # Room where sensor is installed, eg. Outside, Technik, Basteln, UGFlur
             )
  
 
table Measure (
  Id       integer primary key autoincrement,
  DateI    integer, # Insert timestamp unix time
  Sensor   text,    # Sensor ID
  Valuetyp text,    # Value type eg, temperature, humidity
  value    real     # measured value
              )
  
 ------------- Views ----------------

View LogV
    Select *,
           datetime(dateI, 'unixepoch') as STRDate, 
           strftime('%Y',datetime(dateI, 'unixepoch')) as Year,
           strftime('%d',datetime(dateI, 'unixepoch')) as day,
           strftime('%m',datetime(dateI, 'unixepoch'))as month,
           strftime('%H',datetime(dateI, 'unixepoch')) as hour,
           strftime('%M',datetime(dateI, 'unixepoch'))as minute, 
           strftime('%S',datetime(dateI, 'unixepoch')) as second,
    from Log
 
View SensorV
    Select *,
           datetime(dateI, 'unixepoch') as STRDate, 
           strftime('%Y',datetime(dateI, 'unixepoch')) as Year,
           strftime('%d',datetime(dateI, 'unixepoch')) as day,
           strftime('%m',datetime(dateI, 'unixepoch'))as month,
           strftime('%H',datetime(dateI, 'unixepoch')) as hour,
           strftime('%M',datetime(dateI, 'unixepoch'))as minute, 
           strftime('%S',datetime(dateI, 'unixepoch')) as second,
    from Sensor
 
View MeasureV 
    Select *, 
           datetime(dateI, 'unixepoch') as STRDate, 
           strftime('%Y',datetime(dateI, 'unixepoch')) as Year,
           strftime('%d',datetime(dateI, 'unixepoch')) as day,
           strftime('%m',datetime(dateI, 'unixepoch'))as month,
           strftime('%H',datetime(dateI, 'unixepoch')) as hour,
           strftime('%M',datetime(dateI, 'unixepoch'))as minute, 
           strftime('%S',datetime(dateI, 'unixepoch')) as second,
     from Measure
 

create view latestvaluelabor1 as 
     SELECT value 
     FROM Measure_V_A 
     WHERE sensor = 'Labor-1' 
     ORDER BY dateI DESC 
     LIMIT 1;
 

SELECT datetime(dateI, 'unixepoch') as STRDate, strftime('%Y',datetime(dateI, 'unixepoch')) as Year
, strftime('%d',datetime(dateI, 'unixepoch')) as day
, strftime('%m',datetime(dateI, 'unixepoch'))as month
, strftime('%H',datetime(dateI, 'unixepoch')) as hour
, strftime('%M',datetime(dateI, 'unixepoch'))as minute
, strftime('%S',datetime(dateI, 'unixepoch')) as second from Daten