Search code examples
mysqlformatdatetime

How to Change Datetime Format in an MySQL Table

Format datetime in Mysql existing table

First issue:

in mysql table, some of datetime are in 'Y-m-d H:01:00' format.

i would like format them in 'Y-m-d H:00:00' format.

Second issue:

in an another table, i would like format the existing datetime to the closest 'Y-m-d H:05:00' or 'Y-m-d H:10:00' or 'Y-m-d H:15:00' or 'Y-m-d H:20:00'....

How could i change the datas in the mySql Table?

Update:

I found for the first issue : 

UPDATE `table` SET timeStamp = date_format(timeStamp,'%Y-%m-%d %H:00:00')
Solution

  • I would not use the date_format() function for any of the queries, since you do not want to format the date values, you want to change them!

    1) Use minute() and second() functions to determine the minute and second parts of the timestamp and deduct 1 minute from the value:

    UPDATE `table` SET timeStamp = timeStamp - INTERVAL 1 MINUTE
WHERE minute(timeStamp)=1 and second(timeStamp)=0

    2) See "How to round a time to the nearest 15 minute segment" SO topic for an answer. Just replace 900 seconds (15 minutes) with the number of seconds in 5 hours.