Beyond ADDTIME: Exploring Alternatives for Time Calculations in MariaDB

What is ADDTIME?

The ADDTIME function is your go-to tool in MariaDB for adding a specified time interval to a time or datetime value. It essentially takes two arguments and gives you back a new time or datetime that reflects the addition.

Syntax Breakdown

ADDTIME(expr1, expr2)

• expr2: This is the second operand, and it's a time expression. It specifies the amount of time you want to add to expr1.
• expr1: This is the first operand, and it can be either a time or datetime expression. It represents the starting point for your time manipulation.

Examples to Illustrate

Let's get hands-on with some examples to solidify the concept:

1. Adding 30 minutes to a specific time:

SELECT ADDTIME('10:20:00', INTERVAL 30 MINUTE);

This query will return:

10:50:00

1. Adding 2 hours and 15 minutes to a datetime value:

SELECT ADDTIME('2024-07-06 13:00:00', INTERVAL 2 HOUR 15 MINUTE);

Here, the result will be:

2024-07-06 15:15:00

• The result of ADDTIME can overflow or underflow beyond the valid range of time or datetime values. MariaDB might return unexpected results in such cases.
• If expr1 is a date value, MariaDB will automatically convert it to a datetime value with a time of '00:00:00' before performing the addition.
• expr2 must be a valid time expression. You can specify values for hours, minutes, and seconds.

---

1. Adding different time intervals:

-- Adding 1 hour and 30 seconds
SELECT ADDTIME('09:15:00', INTERVAL 1 HOUR 30 SECOND);

-- Adding 45 minutes (notice the shortcut for minutes)
SELECT ADDTIME('12:40:25', INTERVAL 45 MINUTE);

-- Adding a combination of hours, minutes, and seconds
SELECT ADDTIME('18:00:00', INTERVAL 2 HOUR 10 MINUTE 20 SECOND);

1. Working with dates and times:

-- Adding 1 day and 5 hours to a datetime value
SELECT ADDTIME('2024-07-04 10:30:15', INTERVAL 1 DAY 5 HOUR);

-- Subtracting time (achieved by using a negative interval)
SELECT ADDTIME('14:20:00', INTERVAL -30 MINUTE);  -- Returns 13:50:00

1. Using ADDTIME in a SELECT statement:

SELECT product_name, ADDTIME(order_placed, INTERVAL 1 HOUR) AS estimated_delivery
FROM orders;

---

1. Interval Arithmetic
   

datetime_value + INTERVAL time_interval
datetime_value - INTERVAL time_interval

For example:

SELECT '2024-07-06 11:00:00' + INTERVAL 2 HOUR 30 MINUTE;

This achieves the same result as ADDTIME('2024-07-06 11:00:00', INTERVAL 2 HOUR 30 MINUTE).

1. CASE Statement
   

For more complex time calculations involving conditional logic, you can utilize a CASE statement. This approach allows you to define different time manipulations based on specific conditions.

SELECT order_id,
       CASE WHEN order_type = 'urgent' THEN order_placed + INTERVAL 30 MINUTE
            ELSE order_placed + INTERVAL 1 HOUR
       END AS estimated_delivery
FROM orders;

This query assigns an estimated delivery time based on the order type:

• Regular orders get 1 hour added.
• Urgent orders get 30 minutes added.

• If you need conditional logic in your time manipulation, a CASE statement offers more flexibility.
• For simple time additions, ADDTIME or interval arithmetic might be sufficient.