Sql of note/Pivoting in sql: Difference between revisions
From Federal Burro of Information
Jump to navigationJump to search
(Created page with " thdata: <pre> sqlite> select * from thedata limit 10; collecttime|sensor1|sensor2 1525694702|685|255 1525695002|694|262 1525695302|706|267 1525695602|706|269 1525695902|700|...") |
No edit summary |
||
| Line 15: | Line 15: | ||
1525697102|694|259 | 1525697102|694|259 | ||
1525697402|698|261 | 1525697402|698|261 | ||
</pre> | |||
How many record for each hour ? | |||
<pre> | |||
SELECT count(*), hour | |||
from ( | |||
SELECT | |||
date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day, | |||
strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour, | |||
avg(sensor1) as theavg | |||
from thedata | |||
group by day , hour | |||
) | |||
group by hour; | |||
</pre> | </pre> | ||
Revision as of 21:30, 7 November 2018
thdata:
sqlite> select * from thedata limit 10; collecttime|sensor1|sensor2 1525694702|685|255 1525695002|694|262 1525695302|706|267 1525695602|706|269 1525695902|700|267 1525696202|700|266 1525696502|697|264 1525696802|695|261 1525697102|694|259 1525697402|698|261
How many record for each hour ?
SELECT count(*), hour
from (
SELECT
date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day,
strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour,
avg(sensor1) as theavg
from thedata
group by day , hour
)
group by hour;
