MySQL Interval

The MySQL INTERVAL keyword is used to perform date and time arithmetic. It allows you to add or subtract a specific amount of time to or from a date or time value. The INTERVAL keyword is typically used in conjunction with the DATE_ADD() and DATE_SUB() functions.

The basic syntax for using INTERVAL is as follows:

DATE_ADD(date_value, INTERVAL value unit)

or

DATE_SUB(date_value, INTERVAL value unit)

• date_value: The date or time value to which you want to add or subtract an interval.
• value: The numeric value of the interval.
• unit: The unit of time for the interval, such as ‘YEAR’, ‘MONTH’, ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’, etc.

Here are some examples of how to use the INTERVAL keyword:

1. Add 3 days to a date:

   SELECT DATE_ADD('2023-10-15', INTERVAL 3 DAY);

This will add 3 days to the date ‘2023-10-15’, resulting in ‘2023-10-18’.

2. Subtract 2 hours from a datetime:

   SELECT DATE_SUB('2023-10-15 12:30:00', INTERVAL 2 HOUR);

This will subtract 2 hours from the datetime ‘2023-10-15 12:30:00’, resulting in ‘2023-10-15 10:30:00’.

3. Add 6 months to a date:

   SELECT DATE_ADD('2023-10-15', INTERVAL 6 MONTH);

This will add 6 months to the date ‘2023-10-15’, resulting in ‘2024-04-15’.

4. Subtract 30 minutes from a time:

   SELECT DATE_SUB('12:45:00', INTERVAL 30 MINUTE);

This will subtract 30 minutes from the time ’12:45:00′, resulting in ’12:15:00′.

The INTERVAL keyword is quite versatile and can be used for various date and time calculations, making it a powerful tool when working with date and time values in MySQL queries.

Keep in mind that the specific date and time units (e.g., ‘DAY’, ‘HOUR’, ‘MINUTE’) are case-insensitive in MySQL, so you can use upper or lower case as needed.