Round timestamp down to nearest minute

Question

I have some existing data that I am trying to update, off the back of a scheduled task that runs in my application.

The scheduled task fetches some data and then inserts it in to my database, which results in timestamps that are just a few seconds off a rounded second (e.g 2017-14-03 08:00:02).

I am trying to perfect a select statement before I use it in an update. So far I have this:

SELECT server_name, connected_count, TIME_FORMAT( SEC_TO_TIME( (TIME_TO_SEC(created_at) DIV 60) * 60 ), '%Y-%m-%d %H:%i:%s') AS rounded_time FROM `server_tracking`

This works perfectly for the time, rounding it down to the nearest minute, however it drops the date; leaving me with 0000-00-00 08:00:00.

How can I update this to carry the date over too?

Answer 1

You can simply use DATE_FORMAT() and replace the seconds with 0:

SELECT 
server_name, 
connected_count, 
DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:00') AS rounded_time 
FROM `server_tracking`