How to update only the hour from a DATETIME field in MySQL?

M.A.K. Ripon picture M.A.K. Ripon · Jul 4, 2017 · Viewed 13.1k times · Source

I want to update a DateTime column where it will change the hours only without modifying anything else. I can't use to add an interval as the values all are different. There is many different dates too. So need to change the hour of exact desired date as where condition.

Ex:

*************************************************
**        Before       *|*         After       **
************************|************************
** 2017-07-24 19:06:15 *|* 2017-07-24 15:06:15 **
** 2017-07-24 17:12:23 *|* 2017-07-24 15:12:23 **
** 2017-07-24 23:00:03 *|* 2017-07-24 15:00:03 **
** 2017-07-24 20:33:56 *|* 2017-07-24 15:33:56 **
** 2017-07-24 18:19:31 *|* 2017-07-24 15:19:31 **
** 2017-07-24 16:43:47 *|* 2017-07-24 15:43:47 **
*************************************************

Want to do it with MySQL query only without using any programming language.

Answer

Steve Chambers picture Steve Chambers · Oct 3, 2017

SQL

UPDATE datetimes
SET datetime = DATE_ADD(datetime,
                        INTERVAL (15 - HOUR(datetime)) HOUR);

Demo

http://rextester.com/JOJWJ94999

Explanation

DATE_ADD(datetime, INTERVALintervalHOUR) adds or subtracts interval hours from datetime (depending on whether interval is positive or negative). The number of hours to add or subtract is calculated by subtracting the number of hours part of datetime (found from HOUR(datetime)) from 15. If the current time is 16:00 or after, this will be negative and if the current time is before 15:00, it will be a positive number. There is no WHERE clause so all rows in the table will be updated.