SQL all rows with a minimum interval between

Question

I have a table with only two fields (in SQLite3, but I can use PostgreSQL or SQLServer), that have a datetime and a int value (temperature of a room), like below:

+-----------------------------------+
| Table: Temperature                |
+---------------------+-------------+
| Date                | Temperature |
+---------------------+-------------+
| 2017-03-03 15:10:00 |          25 |
| 2017-03-03 15:11:00 |          25 |
| 2017-03-03 15:12:00 |          25 |
| 2017-03-03 15:13:00 |          25 |
| 2017-03-03 15:14:00 |          26 |
| 2017-03-03 15:15:00 |          26 |
| 2017-03-03 15:16:00 |          26 |
| 2017-03-03 15:17:00 |          26 |
| 2017-03-03 15:18:00 |          26 |
| 2017-03-03 15:19:00 |          27 |
| 2017-03-03 15:20:00 |          27 |
+---------------------+-------------+

Like in the example, I have a temperature at every minute.

This is used to monitor the temperature for other systems, but I like to show this in a web page too, using python (flask), but in the web page it's not important to show the temperature at every minute, but at every 10 minutes or at every hour is better.

How to make a select to show only temperatures at every 10 minutes (or another interval)? So my results must be:

+-----------------------------------+
| Table: Temperature                |
+---------------------+-------------+
| Date                | Temperature |
+---------------------+-------------+
| 2017-03-03 15:10:00 |          25 |
| 2017-03-03 15:20:00 |          27 |
+---------------------+-------------+

SqlZim · Accepted Answer · 2017-03-03T18:31:07.297

3

For sql-server you could do something like this:

select * 
from temperature 
where datepart(minute,[date])%10 = 0

for postgres you could use something like this:

select *
from temperature 
where cast(date_part('minute',date) as int) % 10 = 0;

both return:

+---------------------+-------------+
|        date         | temperature |
+---------------------+-------------+
| 2017-03-03 15:10:00 |          25 |
| 2017-03-03 15:20:00 |          27 |
+---------------------+-------------+

sql server rextester demo: http://rextester.com/DXMCK81428

postgres rextester demo: http://rextester.com/NAXX57760

edited Mar 03 '17 at 18:31

answered Mar 03 '17 at 18:23

SqlZim

37,248
6
41
59

based in your answer, I made this for SQLite3: `SELECT * FROM temperature WHERE CAST(strftime('%M', datetime(date)) as int) % 10 = 0` – Roberto Correia Mar 03 '17 at 18:35
@RobertoCorreia Nice! Good translation to sql-lite – SqlZim Mar 03 '17 at 18:36

score 1 · Answer 2 · edited May 23 '17 at 11:53

1

SELECT * FROM table LIMIT -1 OFFSET 10

From this post

edited May 23 '17 at 11:53

Community

1
1

answered Mar 03 '17 at 18:27

jfatal

245
1
3

I liked this, because is short. But, although I said that every row is inserted at every minute, maybe some error can occur in serial port and the next row will be inserted at 2 minutes or more, and then, all the next rows will be wrong. – Roberto Correia Mar 03 '17 at 18:34

Kjetil S. · Answer 3 · 2017-03-03T19:10:35.857

select * from temperature where substr(date,16,1)='0' order by date

An improvement could be to return the average temperature in each ten minute period. This would also make it more robust against missing meassurements:

select
  'From '||min(date)||' to '||max(date)  period,
  sum(1)                                 meassurements,
  avg(temp)                              avg_temp
from temperature
group by substr(date,1,15)
order by 1

Replace 15 with 14 to show for hour periods instead.

The next step could be to also show periods that miss meassurements altogether. This is also possible to do with just one query. I don't show that here.

SQL all rows with a minimum interval between

3 Answers3