Table of Contents
In MySQL, hours can be added to a datetime by using the DATE_ADD function. This function takes in three parameters – the datetime value, the interval of time to be added, and the unit of time (such as hours or days). This allows for the datetime to be increased by a specific amount of time. Alternatively, the ADDTIME function can also be used to add a specific time value (in the format of ‘HH:MM:SS’) to a datetime. Both of these methods can be used to add hours to a datetime in MySQL.
You can use the DATE_ADD() function in MySQL to add a specific number of hours to a datetime field in MySQL.
For example, you can use the following syntax to create a new column that adds 3 hours to the existing datetime in the sales_time column of a table named sales:
SELECT sales_time, DATE_ADD(sales_time, INTERVAL 3 HOUR)
FROM sales;
The following example shows how to use this syntax in practice.
Example: How to Add Hours to Datetime in MySQL
Suppose we have the following table named sales that contains information about sales made at various grocery stores at various times:
-- create table
CREATE TABLE sales (
store_ID INT PRIMARY KEY,
item TEXT NOT NULL,
sales_time DATETIME NOT NULL
);
-- insert rows into table
INSERT INTO sales VALUES (0001, 'Oranges', '2024-02-10 03:45:00');
INSERT INTO sales VALUES (0002, 'Apples', '2020-11-25 15:25:01');
INSERT INTO sales VALUES (0003, 'Bananas', '2009-06-30 09:01:39');
INSERT INTO sales VALUES (0004, 'Melons', '2024-01-14 03:29:55');
INSERT INTO sales VALUES (0005, 'Grapes', '2023-05-19 23:10:04');
-- view all rows in table
SELECT * FROM sales;
Output:
+----------+---------+---------------------+
| store_ID | item | sales_time |
+----------+---------+---------------------+
| 1 | Oranges | 2024-02-10 03:45:00 |
| 2 | Apples | 2020-11-25 15:25:01 |
| 3 | Bananas | 2009-06-30 09:01:39 |
| 4 | Melons | 2024-01-14 03:29:55 |
| 5 | Grapes | 2023-05-19 23:10:04 |
+----------+---------+---------------------+ Suppose that we would like to select the times from the sales_time column and create a new column that adds 3 hours to each time in the sales_time column.
We can use the following syntax to do so:
SELECT sales_time, DATE_ADD(sales_time, INTERVAL 3 HOUR)
FROM sales; Output:
+---------------------+---------------------------------------+
| sales_time | DATE_ADD(sales_time, INTERVAL 3 HOUR) |
+---------------------+---------------------------------------+
| 2024-02-10 03:45:00 | 2024-02-10 06:45:00 |
| 2020-11-25 15:25:01 | 2020-11-25 18:25:01 |
| 2009-06-30 09:01:39 | 2009-06-30 12:01:39 |
| 2024-01-14 03:29:55 | 2024-01-14 06:29:55 |
| 2023-05-19 23:10:04 | 2023-05-20 02:10:04 |
+---------------------+---------------------------------------+
Notice that each of the times in the new column display the time in the sales_time with three hours added to it.
If you’d like, you can also use the AS statement to give a specific name to this new column:
SELECT sales_time, DATE_ADD(sales_time, INTERVAL 3 HOUR) AS threehours
FROM sales; +---------------------+---------------------+
| sales_time | threehours |
+---------------------+---------------------+
| 2024-02-10 03:45:00 | 2024-02-10 06:45:00 |
| 2020-11-25 15:25:01 | 2020-11-25 18:25:01 |
| 2009-06-30 09:01:39 | 2009-06-30 12:01:39 |
| 2024-01-14 03:29:55 | 2024-01-14 06:29:55 |
| 2023-05-19 23:10:04 | 2023-05-20 02:10:04 |
+---------------------+---------------------+ Notice that the new column is now named threehours, which is much easier to read.
Note: If you would like to subtract a specific number of hours then you can use the DATE_SUB() function instead.
Additional Resources
The following tutorials explain how to perform other common tasks in MySQL: