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
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');
SELECT DATE_FORMAT('2009-10-04 22:23:22.023', '%Y-%m-%d %H:%i:00.000');