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.
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.
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.
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.
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?
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With