Group timestamps every 30 minutes

Question

I try to group my tag 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 as follows:

 2016-03-09 00:00:23 2016-03-09 00:35:02 2016-03-09 01:00:03 

The request I use is

 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 get the desired results? I researched other answers on SO and not answers helped

Answer 1

Here's the basic query for grouping by an interval of 30 minutes.

 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 cause an incorrect exit. Use the following query instead. Take a look at the following example:

 SELECT ROUND(3.7), ROUND(4.2); 

Result: 4 4 .

Both are in the same segment. The same applies to the query above, while rounding the timestamp different segments may fall into the same segment, which leads to an incorrect exit

[Recommended next request]

 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 accept the following request.

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

Related party

Answer 2

One method is to use to_seconds() , truncate the value, and then to_seconds() 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;