Group mysql query by 15 min intervals

Question

I've got a monitoring system that is collecting data every n seconds (n is approximately 10 but varies). I'd like to aggregate the collected data by 15 minute intervals. Is there a way to consolidate the timestamp values into 15 minute chunks to allow for grouping to work?

unutbu · Accepted Answer · 2017-11-15T19:22:21.020

78

SELECT   FLOOR(UNIX_TIMESTAMP(timestamp)/(15 * 60)) AS timekey
FROM     table
GROUP BY timekey;

edited Nov 15 '17 at 19:22

answered May 08 '10 at 12:22

unutbu

842,883
184
1,785
1,677

4

Shouldn't it be `UNIX_TIMESTAMP(timestamp)/(15 * 60)` for minutes? – Alexander Gladysh May 08 '10 at 12:25
2

I don't think ROUND is the right function should be used here. DIV is more appropriate. `UNIX_TIMESTAMP(timestamp) DIV (15 * 60)` Because SELECT ROUND(1.8), ROUND(2.1) will fall in the same segment, though they shouldn't. Ref: http://stackoverflow.com/a/3086470/412786 – tzzzoz Nov 19 '15 at 15:47
`ROUND` gives a different 15 minute interval than `DIV`, but both produce 15 minute intervals. – unutbu Nov 19 '15 at 17:32
3

I think using FLOOR will produce better result than ROUND function : 00:00:00 will be grouped with values up to 00:14:59. I think it is a more natural concept. – kmas Nov 15 '17 at 14:07
I would use CEIL. If you use 10 min interval, then FLOOR gives 5,15,25 but ceil gives 0, 10, 20 which is more correct splitting – eSKon Jun 24 '19 at 05:17
`FLOOR(UNIX_TIMESTAMP(timestamp)/(15 * 60)) * 15 * 60` if you want to get the correct timestamp out of the query – Tofandel Aug 07 '20 at 10:17

score 21 · Answer 2 · answered Jun 09 '11 at 09:27

21

Try this , grouping of records of 15 minutes interval, you can change 15*60 to the interval in seconds you need

SELECT sec_to_time(time_to_sec(datefield)- time_to_sec(datefield)%(15*60)) as intervals from tablename
group by intervals

answered Jun 09 '11 at 09:27

BenG

211
2
2

1

I like this approach the most because it keeps the minute interval as one of the columns imo making it more readable. Thanks. – MrB Sep 30 '12 at 18:29
This also helped me! Thanks. Needed it for hours so I changed (15*60) to (60*60) – plocks Jun 18 '15 at 10:02
3

This doesn't work if the records contain multiple dates, because then all records with the same time across dates are grouped. – hepabolu May 07 '17 at 17:46
@BenG: Thanks a lot. This just gives me the result I need. – vijay v Oct 06 '20 at 13:59

MartyIX · Answer 3 · 2010-05-08T12:37:50.810

Adaptation of approach 1) below:

select Round(date_format(date, "%i") / (15*60)) AS interval  
from table
group by interval

Adaptation of approach 3) below:

SELECT Round(Convert(substring(date_column, 14, 2), UNSIGNED) / (15*60)) AS interval /* e.g. 2009-01-04 12:20:00 */
FROM table 
GROUP BY interval;

A few approaches I've found here:

1)

select date_format(date, "%W") AS `Day of the week`, sum(cost)
from daily_cost
group by `Day of the week` 
order by date_format(date, "%w")

2)

select count(*) as 'count', 
  date_format(min(added_on), '%Y-%M-%d') as 'week commencing',
  date_format(added_on, '%Y%u') as 'week' 
from system 
where added_on >= '2007-05-16' 
group by week 
order by 3 desc;

3)

SELECT substring(postdate, 1,10) AS dd, COUNT(id) FROM MyTable GROUP BY dd;

(Also here: http://www.bradino.com/mysql/dayparting-on-datetime-field-using-substring/)

EDIT: All the solutions will perform badly on a table with a large number of records.

well - how would you adapt these to group by 15 minute ranges? — nickf, May 08 '10 at 12:31

score 4 · Answer 4 · answered May 20 '13 at 22:29

I started with the answer given above by unutbu but didn't get what I needed and had to add a bit to it.

Select Created, from_unixtime(FLOOR(UNIX_TIMESTAMP(Created)/(15*60))*(15*60)) GroupTime, COUNT(*) as Cnt FROM issue i GROUP BY GroupTime

This code divides by the 900 seconds in a 15 minute span then floors the value and multiplies it back up by 900, essentially rounding down to the nearest 15 minute increment.

score 4 · Answer 5 · answered Nov 09 '16 at 17:09

Following query groups rows and creates timestamps at 15 min intervals.

Select concat( date(created_dt) , ' ', sec_to_time(time_to_sec(created_dt)- time_to_sec(created_dt)%(15*60) + (15*60)))as created_dt_new from table_name group by created_dt_new

E.g Timestamps
2016-11-09 13:16:29
2016-11-09 13:16:49
2016-11-09 13:17:06
2016-11-09 13:17:26
2016-11-09 13:18:24
2016-11-09 13:19:59
2016-11-09 13:21:17

Are grouped into 2016-11-09 13:30:00

sec_to_time(time_to_sec(created_dt)- time_to_sec(created_dt)%(15*60) + (15*60)))
Upper bounds time to nearest 15 min interval. e.g 12:10 -> 12:15
concat( date(created_dt) , ' ', sec_to_time(time_to_sec(created_dt)- time_to_sec(created_dt)%(15*60) + (15*60)))
Generates a timestamp taking the date from the timestamp field.

score 3 · Answer 6 · answered Sep 18 '18 at 08:49

Unix timestamps: floor them to nearest 15 minute using one of the following:

timestamp div (15 * 60) * (15 * 60) -- div is integer division operator
timestamp - timestamp % (15 * 60)

Date time: assuming the datatype does not have fractional seconds, floor them to nearest 15 minute using:

date - INTERVAL EXTRACT(SECOND FROM date) SECOND - INTERVAL EXTRACT(MINUTE FROM date) % 15 MINUTE

DBFiddle

score 1 · Answer 7 · edited Oct 28 '12 at 03:20

This worked for me

mysql> **SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())- UNIX_TIMESTAMP(NOW())%(15*60));**
+---------------------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())- UNIX_TIMESTAMP(NOW())%(15*60)) |
+---------------------------------------------------------------------+
| 2012-02-09 11:15:00                                                 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

score 0 · Answer 8 · edited Jun 20 '19 at 06:48

0

THis Work for me

SELECT CONCAT (
        YEAR(transactionDate)
        ,'-'
        ,MONTH(transactionDate)
        ,'-'
        ,DAYOFMONTH(transactionDate)
        ,' '
        ,HOUR(transactionDate)
        ,':'
        ,((floor((MINUTE(transactionDate) / 15)) + 1) * 15) - 1
        ,':59'
        ) AS tmp1
    ,count(*)
FROM tablename
GROUP BY tmp1 limit 20;

edited Jun 20 '19 at 06:48

adiga

34,372
9
61
83

answered Jun 20 '19 at 06:42

Chandrakant Thakkar

938
9
23

score 0 · Answer 9 · answered Aug 01 '19 at 07:05

0

Change "15" to whatever interval you want.

select count(*),
CONCAT(HOUR(col_date),":",(MINUTE(create_date) div 15)*15) as date
from tablename
GROUP BY date
ORDER BY col_date ASC;

answered Aug 01 '19 at 07:05

Robert Chan

13
5

Webist · Answer 10 · 2020-10-03T17:21:59.940

0

I was not satisfied by GROUP BY.

SELECT   datetime
FROM     table
WHERE MOD(MINUTE(TIME(datetime)),15) = 0 AND SECOND(TIME(datetime)) = 0;

edited Oct 03 '20 at 17:21

answered Oct 03 '20 at 16:53

Webist

39
1
9

Group mysql query by 15 min intervals

10 Answers10

Linked