In my database I have the following date-times:
x
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