MySQL: How to Add Minutes to Datetime


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;

Output:

+---------------------+---------------------+
| 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:

MySQL: How to Return All Rows Between Two Dates
MySQL: How to Return All Rows Greater Than Date
MySQL: How to Select Rows where Date is Equal to Today

Leave a Reply

Your email address will not be published. Required fields are marked *