How to Add Hours to Datetime in MySQL Using DATE_ADD()

by

Working with date and time values is an essential part of SQL development. Applications for sales, e-commerce, finance, logistics, customer support, scheduling, analytics, and SaaS frequently need to manipulate datetime values.

One common requirement is to add a specific number of hours to a datetime value in MySQL.

MySQL provides the DATE_ADD() function for this purpose. You can use it to add hours to a DATETIME, TIMESTAMP, or other compatible temporal value.

The basic syntax is:

SELECT DATE_ADD(datetime_column, INTERVAL number HOUR)
FROM table_name;

For example, to add three hours to the sales_time column:

SELECT
    sales_time,
    DATE_ADD(sales_time, INTERVAL 3 HOUR) AS new_time
FROM sales;

This returns the original datetime along with a new value that is exactly three hours later.

Example: How to Add Hours to Datetime in MySQL

Suppose we have a table named sales containing information about products sold at different grocery stores.

The table has a sales_time column containing both the date and time of each sale.

We can create the table using:

CREATE TABLE sales (
    store_ID INT PRIMARY KEY,
    item TEXT NOT NULL,
    sales_time DATETIME NOT NULL
);

Next, insert some sample records:

INSERT INTO sales VALUES
(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');

You can view all records with:

SELECT *
FROM sales;

The output is:

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

Add 3 Hours to a Datetime in MySQL

To add three hours to every value in the sales_time column, use:

SELECT
    sales_time,
    DATE_ADD(sales_time, INTERVAL 3 HOUR) AS new_time
FROM sales;

The query returns:

+---------------------+---------------------+
| sales_time          | new_time            |
+---------------------+---------------------+
| 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 final record crosses midnight. The original value is:

2023-05-19 23:10:04

After adding three hours, it becomes:

2023-05-20 02:10:04

MySQL automatically adjusts the date when the calculation crosses midnight.

How DATE_ADD() Works

The general syntax is:

DATE_ADD(date, INTERVAL value unit)

In our example:

DATE_ADD(sales_time, INTERVAL 3 HOUR)

the components mean:

  • sales_time — the original datetime.
  • INTERVAL — tells MySQL that a time interval is being added.
  • 3 — the number of hours.
  • HOUR — the unit of the interval.

Therefore:

2024-02-10 03:45:00
+ 3 hours
= 2024-02-10 06:45:00

The same calculation can be performed for every row in the table.

Give the New Column a Name Using AS

You can use the AS keyword to assign a readable name to the calculated column.

For example:

SELECT
    sales_time,
    DATE_ADD(sales_time, INTERVAL 3 HOUR) AS threehours
FROM sales;

The output is:

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

A more descriptive alias can make production SQL easier to understand:

SELECT
    sales_time,
    DATE_ADD(sales_time, INTERVAL 3 HOUR) AS sales_time_plus_3_hours
FROM sales;

Using descriptive aliases is especially useful when the query contains multiple calculated date and time fields.

Add Different Numbers of Hours

You can change the number of hours according to your requirements.

To add one hour:

SELECT DATE_ADD(sales_time, INTERVAL 1 HOUR)
FROM sales;

To add two hours:

SELECT DATE_ADD(sales_time, INTERVAL 2 HOUR)
FROM sales;

To add six hours:

SELECT DATE_ADD(sales_time, INTERVAL 6 HOUR)
FROM sales;

To add 12 hours:

SELECT DATE_ADD(sales_time, INTERVAL 12 HOUR)
FROM sales;

To add 24 hours:

SELECT DATE_ADD(sales_time, INTERVAL 24 HOUR)
FROM sales;

Adding 24 hours generally moves the datetime forward by one day.

Add Hours That Cross Midnight

One of the useful features of MySQL date arithmetic is that it automatically handles changes to the date.

For example:

SELECT DATE_ADD(
    '2024-02-10 22:30:00',
    INTERVAL 3 HOUR
) AS new_time;

The result is:

+---------------------+
| new_time            |
+---------------------+
| 2024-02-11 01:30:00 |
+---------------------+

The calculation crosses midnight:

2024-02-10 22:30:00
+ 3 hours
= 2024-02-11 01:30:00

You do not need to manually change the date.

Add Hours That Cross Into the Next Month

DATE_ADD() also handles month boundaries.

For example:

SELECT DATE_ADD(
    '2024-01-31 23:00:00',
    INTERVAL 3 HOUR
) AS new_time;

returns:

2024-02-01 02:00:00

The calculation automatically moves from January 31 into February 1.

Add Hours That Cross Into the Next Year

The same principle applies to year boundaries.

For example:

SELECT DATE_ADD(
    '2024-12-31 23:00:00',
    INTERVAL 3 HOUR
) AS new_time;

returns:

2025-01-01 02:00:00

This is useful for applications that need reliable scheduling or deadline calculations around the end of a year.

Add Hours to the Current Date and Time

You can add hours to the current date and time using NOW().

For example:

SELECT
    NOW() AS current_time,
    DATE_ADD(NOW(), INTERVAL 3 HOUR) AS time_after_3_hours;

The first column shows the current MySQL date and time, while the second column shows the datetime three hours later.

You can use the same approach for other intervals:

SELECT DATE_ADD(NOW(), INTERVAL 6 HOUR);

or:

SELECT DATE_ADD(NOW(), INTERVAL 12 HOUR);

Add Hours to a Specific Datetime

You can also provide a datetime directly rather than using a table column:

SELECT DATE_ADD(
    '2024-02-10 03:45:00',
    INTERVAL 3 HOUR
) AS new_time;

The result is:

+---------------------+
| new_time            |
+---------------------+
| 2024-02-10 06:45:00 |
+---------------------+

This is useful for testing date calculations or creating calculated values in application queries.

Add Hours Using Another Column

The number of hours can also be stored in a separate column.

Suppose you have an appointments table:

CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY,
    appointment_time DATETIME NOT NULL,
    duration_hours INT NOT NULL
);

You can calculate the appointment end time using:

SELECT
    appointment_time,
    duration_hours,
    DATE_ADD(
        appointment_time,
        INTERVAL duration_hours HOUR
    ) AS end_time
FROM appointments;

This allows every appointment to have a different duration.

For example, one appointment could last two hours while another lasts four hours.

Add Hours to a TIMESTAMP Column

DATE_ADD() can also be used with TIMESTAMP values.

For example:

SELECT
    created_at,
    DATE_ADD(created_at, INTERVAL 3 HOUR) AS adjusted_time
FROM events;

However, applications that work across multiple time zones should pay close attention to MySQL’s time-zone behavior, particularly when using TIMESTAMP.

For distributed applications, a common approach is to maintain a consistent storage time zone and convert timestamps for users when presenting them.

How to Subtract Hours in MySQL

If you need to move a datetime backward rather than forward, you can use the DATE_SUB() function.

For example, to subtract three hours:

SELECT
    sales_time,
    DATE_SUB(sales_time, INTERVAL 3 HOUR) AS three_hours_earlier
FROM sales;

For example:

2024-02-10 06:45:00
- 3 hours
= 2024-02-10 03:45:00

The two functions can be summarized as:

DATE_ADD(sales_time, INTERVAL 3 HOUR)

for adding hours, and:

DATE_SUB(sales_time, INTERVAL 3 HOUR)

for subtracting hours.

Add Hours Using Interval Arithmetic

MySQL also supports interval expressions directly:

SELECT
    sales_time + INTERVAL 3 HOUR AS new_time
FROM sales;

This produces the same basic result as:

SELECT
    DATE_ADD(sales_time, INTERVAL 3 HOUR) AS new_time
FROM sales;

The DATE_ADD() version can be easier to understand when writing educational examples or more complex SQL queries because the operation is explicit.

Add Hours and Minutes Together

Sometimes you need to add both hours and minutes.

You can use multiple date calculations or a combined interval expression.

For example:

SELECT
    DATE_ADD(
        DATE_ADD(sales_time, INTERVAL 3 HOUR),
        INTERVAL 30 MINUTE
    ) AS new_time
FROM sales;

This adds three hours and 30 minutes.

You can also use a combined interval:

SELECT
    sales_time + INTERVAL '3:30' HOUR_MINUTE AS new_time
FROM sales;

The first approach can be easier to read, while the combined interval can be convenient when working with a value expressed as hours and minutes.

Practical Uses of Adding Hours to Datetime

Adding hours to a datetime is useful in many real-world applications.

Appointment Scheduling

A healthcare, consulting, or service application might need to calculate the end of an appointment.

For example, if an appointment starts at 9:00 AM and lasts three hours:

SELECT DATE_ADD(
    '2024-06-15 09:00:00',
    INTERVAL 3 HOUR
) AS appointment_end;

The result is:

2024-06-15 12:00:00

Delivery and Logistics

A logistics platform can calculate an estimated delivery time by adding an expected number of hours to an order timestamp:

SELECT
    order_time,
    DATE_ADD(order_time, INTERVAL 3 HOUR) AS estimated_delivery
FROM orders;

This can be useful for order tracking and delivery dashboards.

Customer Support SLAs

A support system might give a customer service team three hours to respond to a priority ticket:

SELECT
    created_at,
    DATE_ADD(created_at, INTERVAL 3 HOUR) AS response_deadline
FROM support_tickets;

This creates a calculated deadline for SLA monitoring.

Data Engineering

Data pipelines frequently use time windows to process events.

For example:

SELECT
    event_time,
    DATE_ADD(event_time, INTERVAL 3 HOUR) AS processing_window_end
FROM events;

This can help define processing or analysis windows.

Financial Applications

Financial systems may calculate processing deadlines or expected settlement times:

SELECT
    transaction_time,
    DATE_ADD(transaction_time, INTERVAL 3 HOUR) AS processing_deadline
FROM transactions;

The actual business rules will depend on the application’s time-zone and operating-calendar requirements.

SaaS Applications

Subscription and SaaS platforms may need to calculate temporary access periods, trial deadlines, scheduled tasks, or background processing windows.

For example:

SELECT
    started_at,
    DATE_ADD(started_at, INTERVAL 3 HOUR) AS expiration_time
FROM temporary_access;

Using DATE_ADD() in a WHERE Clause

You can combine DATE_ADD() with filtering conditions.

Suppose you want to retrieve sales occurring within three hours after a specific starting time:

SELECT *
FROM sales
WHERE sales_time >= '2024-02-10 03:45:00'
  AND sales_time < DATE_ADD(
      '2024-02-10 03:45:00',
      INTERVAL 3 HOUR
  );

This represents the time range:

2024-02-10 03:45:00 <= sales_time < 2024-02-10 06:45:00

Using < for the upper boundary helps create a clean half-open interval and avoids double-counting records when adjacent time windows are used.

Add Hours to Create a Future Deadline

A common use of DATE_ADD() is calculating deadlines.

For example:

SELECT
    ticket_id,
    created_at,
    DATE_ADD(created_at, INTERVAL 3 HOUR) AS deadline
FROM support_tickets;

If a ticket was created at:

2024-02-10 15:00:00

the calculated deadline will be:

2024-02-10 18:00:00

This approach can be used for automated alerts and deadline monitoring.

Common Mistakes to Avoid

Forgetting the INTERVAL Keyword

This is incorrect:

DATE_ADD(sales_time, 3 HOUR)

The correct syntax is:

DATE_ADD(sales_time, INTERVAL 3 HOUR)

Using MINUTE Instead of HOUR

If you want to add three hours, use:

INTERVAL 3 HOUR

not:

INTERVAL 3 MINUTE

The unit determines how MySQL interprets the number.

Overwriting the Original Datetime Unnecessarily

If you only need a calculated value, use SELECT:

SELECT
    sales_time,
    DATE_ADD(sales_time, INTERVAL 3 HOUR) AS new_time
FROM sales;

You do not need to update the underlying table.

If the business requirement is actually to permanently change stored data, then an UPDATE statement would be appropriate, but that is a different operation.

Complete MySQL Example

Here is a complete example that you can copy and run:

CREATE TABLE sales (
    store_ID INT PRIMARY KEY,
    item TEXT NOT NULL,
    sales_time DATETIME NOT NULL
);

INSERT INTO sales VALUES
(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');

SELECT
    sales_time,
    DATE_ADD(sales_time, INTERVAL 3 HOUR) AS three_hours_later
FROM sales;

Expected output:

+---------------------+---------------------+
| sales_time          | three_hours_later   |
+---------------------+---------------------+
| 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 |
+---------------------+---------------------+

Frequently Asked Questions

How do I add 3 hours to a datetime in MySQL?

Use:

SELECT DATE_ADD(sales_time, INTERVAL 3 HOUR)
FROM sales;

How do I add 1 hour to a datetime in MySQL?

Use:

SELECT DATE_ADD(sales_time, INTERVAL 1 HOUR)
FROM sales;

How do I add 24 hours to a datetime?

Use:

SELECT DATE_ADD(sales_time, INTERVAL 24 HOUR)
FROM sales;

This generally moves the datetime forward by one day.

How do I subtract 3 hours from a datetime in MySQL?

Use DATE_SUB():

SELECT DATE_SUB(sales_time, INTERVAL 3 HOUR)
FROM sales;

Can I add hours to the current datetime?

Yes. Use:

SELECT DATE_ADD(NOW(), INTERVAL 3 HOUR);

Can I add a different number of hours for each row?

Yes. If the number of hours is stored in another column, you can use that column in the interval expression:

SELECT
    start_time,
    duration_hours,
    DATE_ADD(
        start_time,
        INTERVAL duration_hours HOUR
    ) AS end_time
FROM appointments;

Does DATE_ADD() automatically handle midnight?

Yes. If adding hours crosses midnight, MySQL automatically changes the date.

For example:

SELECT DATE_ADD(
    '2024-02-10 23:00:00',
    INTERVAL 3 HOUR
);

returns:

2024-02-11 02:00:00

Can DATE_ADD() be used with TIMESTAMP?

Yes. DATE_ADD() can be used with TIMESTAMP values, although applications working across multiple time zones should carefully manage MySQL session and application time-zone settings.

Conclusion

Adding hours to a datetime in MySQL is straightforward with the DATE_ADD() function.

The basic syntax is:

DATE_ADD(datetime_value, INTERVAL number HOUR)

For example:

SELECT
    sales_time,
    DATE_ADD(sales_time, INTERVAL 3 HOUR) AS three_hours_later
FROM sales;

MySQL automatically handles changes to the hour, day, month, and year. This makes DATE_ADD() useful for appointment scheduling, delivery estimates, SLA deadlines, event processing, financial applications, SaaS workflows, and time-based analytics.

When you need to move a datetime backward, use the corresponding DATE_SUB() function:

SELECT
    sales_time,
    DATE_SUB(sales_time, INTERVAL 3 HOUR) AS three_hours_earlier
FROM sales;

Together, DATE_ADD() and DATE_SUB() provide a simple and flexible way to perform hour-based datetime calculations in MySQL.

Tags:

You may also like...

Leave a Reply

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

10 − one =