Skip to content
Advertisement

Always round set seconds / minutes /hours to 00 mysql

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
2 People found this is helpful
Advertisement