Interval Type

We have learned about the date and timestamp data types used to store the Date-Time values. In this section, we will learn more about one of their neighbors, the Interval data type. You will understand how to use the interval data type with some of the use cases we have provided.

Overview

The Interval data type is used to store intervals between periods in time. The interval time is specified using the number of years, months, days, hours, minutes, and seconds to a given timestamp.

Format

quantity unit [quantity unit...] [direction]

Here is the description of each parameter, which is used in the above syntax:

Examples

#Case 1: Select interval with quantity and unit

The following example represents 5 years 4 months 3 days 5 hours 10 minutes 25 seconds by using an INTERVAL:

SELECT INTERVAL '5 years 4 months 2 weeks 3 days 5 hours 10 minutes 25 second';

It will return a result as displayed below:

+----------------------------------+
| interval                         |
+----------------------------------+
| 5 years 4 mons 17 days 05:10:25  |
+----------------------------------+

In the second example, we will figure out the interval of 2 weeks and 10 days ago:

#Case 2: Select interval with “ago”

SELECT INTERVAL '2 weeks 10 days ago';

The result will be displayed in:

• days: as 2 weeks is equal to 14 days.
• negative value: as we used an ago direction.

+------------------+
| interval         |
+------------------+
| -24 days         |
+------------------+

#Case 3: Select interval by setting the negative quantity and unit

Using the following command, we will have an interval of 3600 months ago but in years. The (-) symbol is added before the quantity:

SELECT INTERVAL '-3600' MONTH;

It will return a result as displayed below:

+------------------+
| interval         |
+------------------+
| -300 years       |
+------------------+

#Case 4: Select interval by setting the quantity

In this example, we will get to know the time of 2 years and 4 months from a specified value:

SELECT INTERVAL '2-4';

You will get the following successful output:

+------------------+
| interval         |
+------------------+
| 2 years 4 mons   |
+------------------+

#Case 5: Select interval by setting the unit and time

In the previous example, we selected an interval by years and months with a specified quantity. In this example, we will choose an interval by day and time:

SELECT INTERVAL '5 5:10:06';

You will get the following successful output:

+------------------+
| interval         |
+------------------+
| 5 days 05:10:06  |
+------------------+

#Case 6: Extracting data from an interval

We can extract the interval field values from timestamp with the EXTRACT() function:

SELECT EXTRACT (field FROM interval);

• field: supported time parts are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
• interval: specified timestamp.

Now, we will extract a minute from a specified timestamp below:

SELECT EXTRACT (MINUTE
FROM INTERVAL '2 hours 30 minutes');

You will get 30 as an extracted result:

+-----------+
| extract   |
+-----------+
| 30        |
+-----------+