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
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
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With