How to subtract hours from a datetime in MySQL?

Question

I get a datetime field, that's currently in the query as:

SELECT DATE_FORMAT(x.date_entered, '%Y-%m-%d') AS date FROM x ORDER BY date ASC

What I want to do is to subtract 3 hours from that date (GMT issues), but I can't do it in PHP as PHP only knows the date part, not the time.

score 162 · Accepted Answer · answered May 16 '11 at 16:11

162

SELECT DATE_SUB(column, INTERVAL 3 HOUR)....

but would it not be better to try and sort out the underlying time zone issue instead?

answered May 16 '11 at 16:11

Pekka

Well the GMT isn't exactly a thing I can change (damn legacy) but how to get that in the format I need? `%Y-%m-%d` – Lucas Famelli May 16 '11 at 16:13
@Lucas I'm not sure why you are doing a `DATE_FORMAT()` in the first place - would `DATE(x.date_entered)` not do already? The final line would be `DATE(DATE_SUB(x.date_entered, INTERVAL 3 HOUR))` – Pekka May 16 '11 at 16:16
I wanted to pre-format the date in the format I wanted (YYYY/MM/DD), but I wasn't sure of how to do it in PHP, so I did using SQL. – Lucas Famelli May 16 '11 at 16:19
@Lucas ah, fair enough. Then wrap the DATE_FORMAT around the `DATE_SUB` directive and it should work. – Pekka May 16 '11 at 16:20
But how can we subtract the hours so that we can get ( yerterday date and time stamp like 23:00:00 ) – shzyincu Apr 25 '16 at 10:13
@shzyincu Not sure what you mean? You might want to look into DATE_FORMAT() http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html – Pekka Apr 25 '16 at 14:59

score 8 · Answer 2 · answered Aug 11 '16 at 14:51

8

Assuming you have some timezone issue and know source and destination timezone, you could convert it like so

SELECT DATE_FORMAT(CONVERT_TZ(x.date_entered, 'UTC', 'Europe/Berlin'),
                   '%Y-%m-%d') AS date
FROM x ORDER BY date ASC;

answered Aug 11 '16 at 14:51

webjunkie

1

This is likely a "correct" solution in many cases, but unfortunately it requires the timezone table to have been installed with mysql. Otherwise, you just get NULL. I gave it an up-vote based on those specific timezone issues where it _would_ be the most correct solution. For example, you might have two areas in different time zones, and only one of them uses Daylight Savings, so it will sometimes be 3 hours different, and sometimes 2 hours (or 4). – UncaAlby Aug 02 '18 at 20:06

score 1 · Answer 3 · edited Oct 03 '21 at 14:15

1

Normal select query.

Once applied DATE_ADD() function in MySQL

select lastname, 
    date_add(changedat, interval -24 hour) as newdate
from employee_audit;

lastname and changedat is field name and employee_audit is table name.

edited Oct 03 '21 at 14:15

Dharman

answered Oct 03 '21 at 14:10

Solomon Suraj

3 Answers3