How to subtract hours from a datetime in MySQL?

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.

108259 次浏览

小开

最佳答案

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

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

小开

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;

小开

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.