In my database I have the following date-times:
2019-12-12 01:56:46.825 2019-12-12 02:56:47.831 2019-12-12 02:56:47.831
I want to be able to retrieve these records with the hours, minutes or seconds set to their 00 values:
For example I want to be able to return records with seconds and milliseconds set to 00:
2019-12-12 01:56:00.000 2019-12-12 02:56:00.000 2019-12-12 02:56:00.000
Or return them setting the minutes to zero as well.
2019-12-12 01:00:00.000 2019-12-12 02:00:00.000 2019-12-12 02:00:00.000
How can I write a query to format my results as desired?
I tried :
SELECT DISTINCT concat( date_format(data_log.time,'%Y-%m-%d '), TIME_FORMAT(SEC_TO_TIME(((TIME_TO_SEC(data_log.time) +30) DIV 60) * 60), '%H:%i:%s')) AS result
This returns the result with seconds set to 00 so im getting closer
Advertisement
Answer
If you know which parts of the time you want to get as zero then a simple
SELECT DATE_FORMAT('2009-10-04 22:23:22.023', '%Y-%m-%d %H:00:00.000');
will return
2009-10-23 22:00:00.000
Or
SELECT DATE_FORMAT('2009-10-04 22:23:22.023', '%Y-%m-%d %H:%i:00.000');
will return
2009-10-23 22:23:00.000