How to subtract 3 hours from a datetime in MySQL?

MySQLMySQLi Database

Subtract 3 hours from DateTime in MySQL, using any of the following ways. The first approach is as follows −

Case 1 − Using DATE_ADD()

select date_add(yourColumnName,interval -3 hours) from yourTableName;

Case 2 − Using DATE_SUB()

select date_sub(yourColumnName,interval 3 hours) from yourTableName;

Firstly, use now() to get the current date-time −

mysql> select now();

The following is the output −

+---------------------+
| now()               |
+---------------------+
| 2018-11-30 10:13:23 |
+---------------------+
1 row in set (0.00 sec)

DATE_ADD

The query to subtract 3 hours from DateTime is as follows. With date_add, we have set a negative date −

mysql> select date_add(now(),interval -3 hour);

The following is the output −

+----------------------------------+
| date_add(now(),interval -3 hour) |
+----------------------------------+
| 2018-11-30 07:13:28              |
+----------------------------------+
1 row in set (0.00 sec)

DATE_SUB

Get the current date and time −

mysql> select now();

The output −

+---------------------+
| now()               |
+---------------------+
| 2018-11-30 10:14:12 |
+---------------------+
1 row in set (0.00 sec)

Implement the second method using date_sub(). The query is as follows −

mysql> select date_sub(now(),interval 3 hour);

The following output displays the date subtracting 3 hours −

+---------------------------------+
| date_sub(now(),interval 3 hour) |
+---------------------------------+
| 2018-11-30 07:14:25             |
+---------------------------------+
1 row in set (0.00 sec)
raja
Published on 10-Jan-2019 07:43:27
Advertisements