MySQL Interval

The interval operator in MySQL allows you to perform date and time calculations. This specific operator will enable you to add or subtract a particular date and time interval from a date.

MySQL Create Interval

To create an interval in MySQL, use the syntax as shown below:

INTERVAL expression unit;

We start with the interval keyword, followed by the expression and unit parameters. The expression represents the value while the unit specifies the unit format.

For example, we can create 36 hours interval as shown:

INTERVAL 36 HOUR

Once we have defined an interval, we can use it to perform basic date and time arithmetic operations.

Example 1

For example, let us add 36 hours to the current timestamp:

SELECT CURRENT_TIMESTAMP + INTERVAL '36 HOURS';

The above should add 36 hours to the current timestamp. This should return:

Example 2

You can also subtract specific units from time as shown in the example:

select date '2022-04-08' - interval '10 days'

The above query subtracts ten days from the specified date. This should return:

Example 3

You can also subtract an interval from an interval as shown below:

select interval '2 days' - interval '5 hours'

This should return:

Example 4

Apart from addition and subtraction, you can perform other arithmetic operations as shown below:

select interval '1 day' * 30;

The above will take one day and multiply by 30. This should return 30 days as:

Example 5

You can also perform interval division as shown:

select interval '1 hour' / 60;

The above query takes one hour and divides by 60. therefore, it should return 1 minute as

Closing

In this article, we covered the interval operator in MySQL and PostgreSQL. It allows us to perform arithmetic operations on date and time.

Keep coding!!