dimanche 3 juillet 2016

mysql get all intervals of datetime based on interval hour


I have the following table with datetime type as follows

Id            interval_time                      processed_time
1             2016-07-22 08:00:00                 2016-07-22 09:25:00
2            2016-07-22 09:00:00                   2016-07-22 10:30:00
3            2016-07-22 14:00:00                   2016-07-22 15:23:00
4            2016-07-22 15:00:00                   2016-07-22 14:27:00
5            2016-07-22 16:00:00                   2016-07-22 15:24:00
6            2016-07-22 17:00:00                   2016-07-22 16:40:00

I need to get the all missed interval_time for last 24 hours with the given input time.

I need to get all missed intervals which doesn't exists in table as follows.

2016-07-22 10:00:00
2016-07-22 11:00:00
2016-07-22 12:00:00
2016-07-22 13:00:00

here i can join the table with the list of all intervals of last 24 hours , but how can i get the last 24 hours list with hour as interval.


Aucun commentaire:

Enregistrer un commentaire