I
I
ilya732019-09-19 00:02:45
SQL
ilya73, 2019-09-19 00:02:45

Sampling the maximum hourly value?

We have a table like:
dt text | un integer

2018-12-28 00:18:35.881|208
2018-12-28 00:18:37.095|208
2018-12-28 00:18:40.095|209
2018-12-28 00:18:43.135|209
2018-12-28 00:18:49.155|209
2018-12-28 00:18:52.159|209
2018-12-28 00:18:56.983|209

I want to get the max value of the un field for every hour and during the day.
So far, I haven’t come up with anything smarter, except to send in a loop:
select max(un),dt from d5 where dt between datetime('now','localtime','-1 hours') and datetime('now','localtime','-0 hours');
select max(un),dt from d5 where dt between datetime('now','localtime','-2 hours') and datetime('now','localtime','-1 hours');
.........
select max(un),dt from d5 where dt between datetime('now','localtime','-24 hours') and datetime('now','localtime','-23 hours');

Maybe there is a more correct and beautiful solution?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question