CODEWITHSUNDEEP
phpmysqldatetime
Nikhil Bhatia
Nikhil Bhatia

Reputation: 344

MySql query for records at every interval between two DateTime range

Following MySQL query provides records at times: 08:00:00, 09:00:00, 12:00:00, 15:00:00 etc (divisible by 3, as specified in group by), but I want records for times 08:00:00, 11:00:00, 14:00:00, 17:00:00 etc. i.e. interval from my given start time. How can I achieve this?

SELECT *  
FROM  `data_10082016`  
WHERE CREATED_AT BETWEEN  "2016-08-11 08:00:00" AND  "2016-08-15 08:00:00" 
GROUP BY DATE( DATETIME ) , FLOOR( HOUR( DATETIME ) /3 )  
ORDER BY  `data_10082016`.`id` ASC

Upvotes: 0

Views: 1348

Answers (2)

Olaf Dietsche
Olaf Dietsche

Reputation: 74018

You just add an offset (+2, -7, ...) to your floor(...) function, e.g.

...
group by date(created_at), floor(hour(created_at) / 3) + 2
...

Upvotes: 0

symcbean
symcbean

Reputation: 48357

Assuming that you want the records corresponding to these times rather than an aggregate measure across the intervals between these times....

SELECT *  
FROM  `data_10082016`  
WHERE CREATED_AT BETWEEN  "2016-08-11 08:00:00" AND  "2016-08-15 08:00:00" 
AND MOD(HOUR( DATETIME )+8, 3) = 0
ORDER BY  `data_10082016`.`id` ASC

Will give you records at 3 hour intervals from 8am.

Getting the records closest to the hour is a whole lot more difficult and will need a reference table. Not something to be attempted with a great deal of expertise in SQL and a lit of time.

Upvotes: 1

Related Questions