Table of Contents
Adding minutes to a Datetime in MySQL can be done using the DATE_ADD function. This function takes two parameters – the first being the Datetime value and the second being the number of minutes to be added. The result is a new Datetime value with the specified number of minutes added. This functionality can be useful for performing calculations or manipulating Datetime values in a database.
You can use the DATE_ADD() function in MySQL to add a specific number of minutes to a datetime field in MySQL.
For example, you can use the following syntax to create a new column that adds 30 minutes to the existing datetime in the sales_time column of a table named sales:
SELECT sales_time, DATE_ADD(sales_time, INTERVAL 30 MINUTE)
FROM sales;
The following example shows how to use this syntax in practice.
Example: How to Add Minutes 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 30 minutes 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 30 MINUTE)
FROM sales;
Output:
+---------------------+------------------------------------------+
| sales_time | DATE_ADD(sales_time, INTERVAL 30 MINUTE) |
+---------------------+------------------------------------------+
| 2024-02-10 03:45:00 | 2024-02-10 04:15:00 |
| 2020-11-25 15:25:01 | 2020-11-25 15:55:01 |
| 2009-06-30 09:01:39 | 2009-06-30 09:31:39 |
| 2024-01-14 03:29:55 | 2024-01-14 03:59:55 |
| 2023-05-19 23:10:04 | 2023-05-19 23:40:04 |
+---------------------+------------------------------------------+
Notice that each of the times in the new column display the time in the sales_time column with 30 minutes 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 30 MINUTE) AS addthirty
FROM sales;
+---------------------+---------------------+
| sales_time | addthirty |
+---------------------+---------------------+
| 2024-02-10 03:45:00 | 2024-02-10 04:15:00 |
| 2020-11-25 15:25:01 | 2020-11-25 15:55:01 |
| 2009-06-30 09:01:39 | 2009-06-30 09:31:39 |
| 2024-01-14 03:29:55 | 2024-01-14 03:59:55 |
| 2023-05-19 23:10:04 | 2023-05-19 23:40:04 |
+---------------------+---------------------+
Notice that the new column is now named addthirty, which is easier to read.
Note: If you would like to subtract a specific number of minutes then you can use the DATE_SUB() function instead.
Additional Resources
The following tutorials explain how to perform other common tasks in MySQL: