CODEWITHSUNDEEP
mysqlsqlrounding
Mister Verleg
Mister Verleg

Reputation: 4293

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

Upvotes: 0

Views: 1988

Answers (2)

forpas
forpas

Reputation: 164069

The function str_to_date() works fine in this case:

str_to_date(datecol, '%Y-%m-%d %H:%i')

or

str_to_date(datecol, '%Y-%m-%d %H')

See the demo.

Upvotes: 1

RiggsFolly
RiggsFolly

Reputation: 94642

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

Upvotes: 5

Related Questions