Grouping timestamp every 30 minutes

Question

I'm trying to group my timestamp every 30 minutes.

I want my result to be like this:

2016年03月09日 00:00:00
2016年03月09日 00:30:00
2016年03月09日 01:00:00

Instead, my results are this:

2016年03月09日 00:00:23
2016年03月09日 00:35:02
2016年03月09日 01:00:03

The query that I'm using is this

SELECT timestamp
FROM a.tablename
WHERE name = 'example' AND timestamp LIKE '2016-03-09%'
GROUP BY ROUND(((60/30) * HOUR(timestamp) + FLOOR( MINUTE(timestamp) / 30)));

How can I have my desired results? I've researched other answers on SO and non of the answers helped

Answer 1

Here's the basic query to group by 30 minutes interval.

SELECT 
FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(timestamp)/(30* 60)) * (30*60)) thirtyHourInterval
FROM tablename
GROUP BY ROUND(UNIX_TIMESTAMP(timestamp)/(30* 60));

Note: ROUND() may lead you towards wrong output. Use the following query instead. Look at the following example:

SELECT ROUND(3.7), ROUND(4.2);

Result: 4 4.

Both lies in the same segment. The same holds for the above query while rounding the timestamp of different segments might fall in the same segment thus leading towards wrong output

[The following query is Recommended]

SELECT 
FROM_UNIXTIME((UNIX_TIMESTAMP(`timestamp`) DIV (30* 60) ) * (30*60)) thirtyHourInterval
FROM tablename
GROUP BY UNIX_TIMESTAMP(`timestamp`) DIV (30* 60)

SQL FIDDLE DEMO

---

Alternatively you can adopt the following query.

SELECT 
FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(timestamp)/(30* 60)) * (30*60)) thirtyHourInterval
FROM tablename
GROUP BY ( 4 * HOUR( `timestamp` ) + FLOOR( MINUTE( `timestamp` ) / 30 ));

Relatedpost

• bmarkham: Yeah this is perfect! Unfortunately, I have a wait a couple minutes to accept this. Thanks!
• bmarkham: Yes I used your recommended query and I can confirm it works too

Answer 2

One method is to use to_seconds(), truncate the value, and then re-create the datetime value:

select date_add(0, interval floor(to_seconds(timestamp) / (30 * 60)) second) as timestamp
from a.tablename
where name = 'example' and timestamp >= '2016-03-09' and timestamp < '2016-03-10'
group by date_add(0, interval floor(to_seconds(timestamp) / (30 * 60)) second)
order by 1;

• bmarkham: Although your entire query didn't work in my case, your WHERE clause helped me get just that day. so +1