How to subtract hours from a datetime in MySQL?

mysql datetime gmt
Lucas Famelli picture Lucas Famelli · May 16, 2011 · Viewed 89.6k times · Source

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.

Answer

Pekka picture Pekka · May 16, 2011

mySQL has DATE_SUB():

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

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

Related questions

MySQL query for current GMT time

This sounds simple enough but I haven't been able to figure out how to use a simple SELECT statement to return the current time in GMT. I have been trying to use CONVERT_TZ() to convert NOW() to GMT based …

Read More
How do you set a default value for a MySQL Datetime column?

How do you set a default value for a MySQL Datetime column? In SQL Server it's getdate(). What is the equivalant for MySQL? I'm using MySQL 5.x if that is a factor.

Read More
Should I use the datetime or timestamp data type in MySQL?

Would you recommend using a datetime or a timestamp field, and why (using MySQL)? I'm working with PHP on the server side.

Read More