Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 856
Lucas Famelli Avatar asked May 16 '11 16:05

Lucas Famelli


People also ask

How do I subtract hours from a timestamp?

To subtract hours from a given timestamp, we are going to use the datetime and timedelta classes of the datetime module. Step 1: If the given timestamp is in a string format, then we need to convert it to the datetime object. For that we can use the datetime. strptime() function.

How do you subtract time in SQL?

Time subtraction: result = time1 - time2 If MINUTE( TIME2 ) <= MINUTE( TIME1 ) then MINUTE( RESULT ) = MINUTE( TIME1 ) - MINUTE( TIME2 ) . If MINUTE( TIME2 ) > MINUTE( TIME1 ) then MINUTE( RESULT ) = 60 + MINUTE( TIME1 ) - MINUTE( TIME2 ) and HOUR( TIME2 ) is incremented by 1.

How do you subtract in MySQL?

Since MySQL does not provide support for MINUS operator. However, we can use a LEFT JOIN clause to simulate this operator. We can use the following syntax to simulate the MINUS operator: SELECT column_list FROM table1.


2 Answers

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?

like image 159
Pekka Avatar answered Oct 16 '22 22:10

Pekka


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; 
like image 27
webjunkie Avatar answered Oct 16 '22 23:10

webjunkie