How to update only the hour from a DATETIME field in MySQL?
Solution 1
SQL
UPDATE datetimes
SET datetime = DATE_ADD(datetime,
INTERVAL (15 - HOUR(datetime)) HOUR);
Demo
http://rextester.com/JOJWJ94999
Explanation
DATE_ADD(datetime, INTERVAL
intervalHOUR)
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.
Solution 2
You can use the DATE_FORMAT() function and "hardcode" the hour:
UPDATE some_table SET dt = DATE_FORMAT(dt, '%Y-%m-%d 15:%i:%s');
Demo: http://rextester.com/RJESF70894
If you want to bind the hour as parameter in a prepared statement, you can combine it with REPLACE()
:
UPDATE some_table SET dt = DATE_FORMAT(dt, REPLACE('%Y-%m-%d %H:%i:%s', '%H', ?))
Demo: http://rextester.com/OHUKF73552
Solution 3
looks like MySQL DATETIME - Change only the date
UPDATE tabelname
SET colname = CONCAT(DATE(colname), ' ', 7, DATE_FORMAT(colname, ':%i:%s'))
WHERE id = 123;
Where 7 stands for the new hour you want for this record's datetime column
Solution 4
You can use below query, hope it'll help you out:
UPDATE tablename SET colname = DATE_FORMAT(STR_TO_DATE(colname, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d 15:%i:%s');
In this query 15 stands for the new hour you want for the record's
Related videos on Youtube
M.A.K. Ripon
I'm a Software Engineer. Working in JCL Group as Manager Of Software Department and Assistant Manager of IT Department at Fashion Flash Limited. Love working with encryption data and playing with algorithms on new language every time.
Updated on September 15, 2022Comments
-
M.A.K. Ripon over 1 year
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.
-
Barmar almost 7 yearsWhere does the new hour come from?
-
-
Paul Spiegel over 6 yearsI like this one, but would write it as
datetime + INTERVAL (15 - HOUR(datetime)) HOUR
. -
Steve Chambers over 6 yearsAgreed - have added the extra brackets.