How the TIMESTAMPADD() function works in Mariadb?

The TIMESTAMPADD() function in MariaDB is used to add an interval to a date or datetime expression. It allows you to increment or decrement a date or datetime value by a specified interval, such as days, hours, minutes, or seconds.

Syntax

The syntax of the MariaDB TIMESTAMPADD() function is as follows:

TIMESTAMPADD(unit, interval, datetime_expr)

• unit: This is the unit of the interval to be added or subtracted. It can be one of the following values:
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
• interval: This is the numeric value representing the number of intervals to be added or subtracted. It can be positive (to add intervals) or negative (to subtract intervals).
• datetime_expr: This is the date or datetime expression to which the interval is added or subtracted.

The function returns a DATETIME value representing the result of adding or subtracting the specified interval to the input date or datetime expression.

Examples

Example 1: Adding days to a date

This example demonstrates how to add days to a date using the TIMESTAMPADD() function.

SELECT TIMESTAMPADD(DAY, 5, '2023-05-01');

The following is the output:

+------------------------------------+
| TIMESTAMPADD(DAY, 5, '2023-05-01') |
+------------------------------------+
| 2023-05-06                         |
+------------------------------------+

In this example, the TIMESTAMPADD() function adds 5 days to the date ‘2023-05-01’, resulting in the date ‘2023-05-06 00:00:00’.

Example 2: Subtracting hours from a datetime

This example shows how to subtract hours from a datetime using the TIMESTAMPADD() function.

SELECT TIMESTAMPADD(HOUR, -3, '2023-05-01 10:30:00');

The following is the output:

+-----------------------------------------------+
| TIMESTAMPADD(HOUR, -3, '2023-05-01 10:30:00') |
+-----------------------------------------------+
| 2023-05-01 07:30:00                           |
+-----------------------------------------------+

In this example, the TIMESTAMPADD() function subtracts 3 hours from the datetime ‘2023-05-01 10:30:00’, resulting in the datetime ‘2023-05-01 07:30:00’.

Example 3: Adding months to a date

This example demonstrates how to add months to a date using the TIMESTAMPADD() function.

SELECT TIMESTAMPADD(MONTH, 6, '2023-05-01');

The following is the output:

+--------------------------------------+
| TIMESTAMPADD(MONTH, 6, '2023-05-01') |
+--------------------------------------+
| 2023-11-01                           |
+--------------------------------------+

In this example, the TIMESTAMPADD() function adds 6 months to the date ‘2023-05-01’, resulting in the date ‘2023-11-01 00:00:00’.

Example 4: Using TIMESTAMPADD() with a table

This example shows how to use the TIMESTAMPADD() function in combination with a table.

DROP TABLE IF EXISTS appointments;
CREATE TABLE appointments (
    id INT PRIMARY KEY,
    appointment_datetime DATETIME
);
INSERT INTO appointments VALUES
    (1, '2023-05-01 09:00:00'),
    (2, '2023-05-15 14:30:00');

SELECT id, appointment_datetime,
       TIMESTAMPADD(HOUR, 2, appointment_datetime) AS new_datetime
FROM appointments;

The following is the output:

+----+----------------------+---------------------+
| id | appointment_datetime | new_datetime        |
+----+----------------------+---------------------+
|  1 | 2023-05-01 09:00:00  | 2023-05-01 11:00:00 |
|  2 | 2023-05-15 14:30:00  | 2023-05-15 16:30:00 |
+----+----------------------+---------------------+

In this example, the TIMESTAMPADD() function is used to add 2 hours to the appointment_datetime column in the appointments table, and the result is included in the output along with the original appointment_datetime and id values.

Example 5: Using TIMESTAMPADD() with multiple units

This example demonstrates how to use the TIMESTAMPADD() function with multiple units to add complex intervals.

SELECT TIMESTAMPADD(MONTH, 3, TIMESTAMPADD(DAY, 10, '2023-05-01'));

The following is the output:

+-------------------------------------------------------------+
| TIMESTAMPADD(MONTH, 3, TIMESTAMPADD(DAY, 10, '2023-05-01')) |
+-------------------------------------------------------------+
| 2023-08-11                                                  |
+-------------------------------------------------------------+

In this example, the TIMESTAMPADD() function first adds 10 days to the date ‘2023-05-01’, resulting in ‘2023-05-11 00:00:00’. Then, it adds 3 months to that result, giving the final output ‘2023-08-11 00:00:00’.

Related Functions

The following are some functions related to the MariaDB TIMESTAMPADD() function:

• MariaDB TIMESTAMPDIFF() function is used to calculate the difference between two date or datetime expressions as an integer.
• MariaDB DATE_ADD() and DATE_SUB() functions are used to add or subtract an interval to or from a date value, respectively.
• MariaDB ADDDATE() and SUBDATE() functions are used to add or subtract days to or from a date value, respectively.

Conclusion

The TIMESTAMPADD() function in MariaDB is a powerful tool for manipulating date and time values by adding or subtracting intervals. It can be used in various scenarios, such as scheduling, date calculations, and data manipulation. By understanding the usage and capabilities of this function, along with related functions, developers can effectively work with date and time data in their MariaDB applications.