sensors

dht11.sql

/* SQLite 3.16.2 */

select
    *
from
    DHT11
where
    ROWID > (select max(ROWID) from DHT11) - 10
union all select 'Max within 24 hours',             max(TEMP) ,             max(HUMIDITY)  from DHT11 where DATE >= datetime('now','-24 hours','localtime')
union all select 'Avg within 24 hours',printf('%2d',avg(TEMP)),printf('%2d',avg(HUMIDITY)) from DHT11 where DATE >= datetime('now','-24 hours','localtime')
union all select 'Min within 24 hours',             min(TEMP) ,             min(HUMIDITY)  from DHT11 where DATE >= datetime('now','-24 hours','localtime')
;

dht11_all.sql

/* SQLite 3.16.2 */

select
    *
from
    DHT11
where
    DATE >= datetime('now','-24 hours','localtime')
;

lm35dz.sql

/* SQLite 3.16.2 */

select
    DATE
,   DATA
,   printf('%1.6f',VOLTS)
,   printf('%2.3f',TEMP)
from
    LM35DZ
where
    ROWID > (select max(ROWID) from LM35DZ) - 10
union all select 'Max within 24 hours','','',printf('%2.3f',max(TEMP)) from LM35DZ where DATE >= datetime('now','-24 hours','localtime')
union all select 'Avg within 24 hours','','',printf('%2.3f',avg(TEMP)) from LM35DZ where DATE >= datetime('now','-24 hours','localtime')
union all select 'Min within 24 hours','','',printf('%2.3f',min(TEMP)) from LM35DZ where DATE >= datetime('now','-24 hours','localtime')
;

lm61.sql

/* SQLite 3.16.2 */

select
    LM61CIZ.DATE
,   printf('%1.6f',LM61CIZ.VOLTS)  ,printf('%2.3f',LM61CIZ.TEMP)
,   printf('%1.6f',LM61BIZ.VOLTS)  ,printf('%2.3f',LM61BIZ.TEMP)
,   printf('%1.6f',LM61CIZx2.VOLTS),printf('%2.3f',LM61CIZx2.TEMP)
from
    LM61CIZ
        left outer join LM61BIZ   on LM61CIZ.DATE = LM61BIZ.DATE
        left outer join LM61CIZx2 on LM61CIZ.DATE = LM61CIZx2.DATE
where
    LM61CIZ.ROWID > (select max(LM61CIZ.ROWID) from LM61CIZ) - 10
;

lm_all.sql

/* SQLite 3.16.2 */

select
    LM61CIZ.DATE
,   LM61CIZ.TEMP
,   LM61BIZ.TEMP
,   LM61CIZx2.TEMP
,   LM35DZ.TEMP
,   (select avg(TEMP) from LM35DZ where date >= datetime('now','-24 hours','localtime'))
from
    LM61CIZ
        left outer join LM61CIZx2 on LM61CIZ.DATE = LM61CIZx2.DATE
        left outer join LM61BIZ   on LM61CIZ.DATE = LM61BIZ.DATE
        left outer join LM35DZ    on LM61CIZ.DATE = LM35DZ.DATE
where
    LM61CIZ.DATE >= datetime('now','-24 hours','localtime')
;

lm_all2.sql

/* SQLite 3.16.2 */

select
    LM61CIZ.DATE
,   LM61CIZ.VOLTS  ,LM61CIZ.TEMP
,   LM61CIZx2.VOLTS,LM61CIZx2.TEMP
,   LM61BIZ.VOLTS  ,LM61BIZ.TEMP
,   LM35DZ.VOLTS   ,LM35DZ.TEMP
from
    LM61CIZ
        left outer join LM61CIZx2 on LM61CIZ.DATE = LM61CIZx2.DATE
        left outer join LM61BIZ   on LM61CIZ.DATE = LM61BIZ.DATE
        left outer join LM35DZ    on LM61CIZ.DATE = LM35DZ.DATE
where
    LM61CIZ.DATE >= datetime('now','-1 hours','localtime')
;

njl7302l.sql

/* SQLite 3.16.2 */

select
    *
from
    NJL7302L
where
    ROWID > (select max(ROWID) from NJL7302L) - 10
union all select 'Max within 24 hours','','',printf('%5.1f',max(LUX)) from NJL7302L where DATE >= datetime('now','-24 hours','localtime')
union all select 'Avg within 24 hours','','',printf('%5.1f',avg(LUX)) from NJL7302L where DATE >= datetime('now','-24 hours','localtime')
union all select 'Min within 24 hours','','',printf('%5.1f',min(LUX)) from NJL7302L where DATE >= datetime('now','-24 hours','localtime')
;

rpi_cpu.sql

/* SQLite 3.16.2 */

select
    DATE
,   ARM_TEMP
,   ARM_FREQ
,   CPU_USAGE
,   VM_USAGE
,   RX_BYTES / 1024.0
,   TX_BYTES / 1024.0
,   DISK_USAGE_ROOT
from
    RPi3
where
    ROWID > (select max(ROWID) from RPi3) - 1800
;

sysmon.sql

/* SQLite 3.16.2 */

select
    TIME
,   CPU_PERCENT
,   VM_PERCENT
,   printf("%.1f", RX_BYTES / 1024.0)
,   printf("%.1f", TX_BYTES / 1024.0)
,   LOGIN_USERS
,   DISK_USAGE_ROOT
from
    SYSMON
where
    ROWID > (select max(ROWID) from SYSMON) - 3600
;