Ava Barbilla Ava Barbilla - 7 months ago 23
SQL Question

How to group time column into 5 minute intervals and max/min value respectively SQL?

I have a database with a Datetime column containing intervals of +/- 30 seconds and a Value column containing random numbers between 10 and 100. My table looks like this:

datetime value
----------------------------
2016-05-04 20:47:20 12
2016-05-04 20:47:40 44
2016-05-04 20:48:30 56
2016-05-04 20:48:40 25
2016-05-04 20:49:30 92
2016-05-04 20:49:40 61
2016-05-04 20:50:00 79
2016-05-04 20:51:20 76
2016-05-04 20:51:30 10
2016-05-04 20:51:40 47
2016-05-04 20:52:40 23
2016-05-04 20:54:00 40
2016-05-04 20:54:10 18
2016-05-04 20:54:50 12
2016-05-04 20:56:00 55


What I want the following output:

datetime max_val min_val
-----------------------------------------
2016-05-04 20:45:00 92 12
2016-05-04 20:50:00 79 10
2016-05-04 20:55:00 55 55


Before I can even continue getting the maximum value and the minimum value, I first have to GROUP the datetime column into 5 minute intervals. According to my research I came up with this:

SELECT
time,
value
FROM random_number_minute
GROUP BY
UNIX_TIMESTAMP(time) DIV 300


Which actually GROUPS the datetime column into 5 minute intervals like this:

datetime
-------------------
2016-05-04 20:47:20
2016-05-04 20:50:00
2016-05-04 20:56:00


This comes very close as it takes the next closest datetime to, in this case,
20:45:00
,
20:50:00
, etc. I would like to rounddown the datetime to the nearest 5 minutes regardless of the seconds, for instance if the minutes are:

minutes rounddown
--------------------
10 10
11 10
12 10
13 10
14 10
15 15
16 15
17 15
18 15
19 15
20 20


The time could be 14:59 and I would like to rounddown to 10:00. I also tried using this after hours of research:

SELECT
time,
time_rounded =
dateadd(mi,(datepart(mi,dateadd(mi,1,time))/5)*5,dateadd(hh,datediff(hh,0,dateadd(mi,1,time)),0))


But sadly this did not work. I get this error:

Incorrect parameter count in the call to native function 'datediff'

I tried this too:

SELECT
time, CASE
WHEN DATEDIFF(second, DATEADD(second, DATEDIFF(second, 0, time_out) / 300 * 300, 0), time) >= 240
THEN DATEADD(second, (DATEDIFF(second, 0, time) / 300 * 300) + 300, 0)
ELSE DATEADD(second, DATEDIFF(second, 0, time) / 300 * 300, 0)
END


Returning the same error.

How can I do this? And after the datetime is grouped, how can I get the max and min value of the data grouping?

Answer

Sorry if I'm repeating another answer. I'll delete if I am..

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(datetime)/300)*300) x
     , MIN(value) min_value
     , MAX(value) max_value 
  FROM my_table 
 GROUP 
    BY x;