How to subtract hours from a datetime in MySQL?

MysqlDatetimeGmt

Mysql Problem Overview

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.

Mysql Solutions

Solution 1 - Mysql

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?

Solution 2 - Mysql

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;

Solution 3 - Mysql

Normal select query. enter image description here

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. enter image description here

Categories
Recommended Mysql Solutions
Recommended Datetime Solutions
Recommended Gmt Solutions

Previous Solution:

TortoiseGit vs Git Extensions

GitTortoisegitGit Extensions

Next Solution:

Does Dapper support the like operator?

SqlDapper

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionLucas FamelliView Question on Stackoverflow
Solution 1 - MysqlPekkaView Answer on Stackoverflow
Solution 2 - MysqlwebjunkieView Answer on Stackoverflow
Solution 3 - MysqlSolomon SurajView Answer on Stackoverflow