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

Question

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.

Megalopolis

Where does the new hour come from? – Suppository

@Suppository it might come from a paremeter. It would be nice to see what sql he's tried so far – Seagirt

Use string concatenation to merge the new hour into a format string that you use with DATE_FORMAT(). – Suppository

if you want to update the data directly, simply use substring. If you always consign a two digit value for hour this will work.e.g. update schema_name.table_name set substring(date_field,12,2)=new_hour where date_field=where_condition. please let me now if this is what you are looking for and i will add an answer – A1

Answer 1

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.

Liquefacient

I like this one, but would write it as datetime + INTERVAL (15 - HOUR(datetime)) HOUR. – Facing

Agreed - have added the extra brackets. – Liquefacient

Answer 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

Facing

Answer 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

Except

#1305 - FUNCTION DATE_FOMAT does not exist – Megalopolis

R is missing: DATE_FORMAT – Except

#1582 - Incorrect parameter count in the call to native function 'DATE_FORMAT' – Megalopolis

colname should be mentioned as well. (it's still early :-) ) – Except

It changes minutes and seconds to 00:00. I want to put them unchanged. – Megalopolis

% missing: :%i:%s – Except

Answer 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

Rambort