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
Here is the description of each parameter, which is used in the above syntax:
Parameters | Description |
quantity | A specified number (can be applied with a - sign to separate each quantity). |
unit | - A year, month, week, day, hour, minute, second. - An abbreviation can be y, m, d, and so on: YYYY-MM-DD [HH:MM:SS]. - The plural forms can be months, days, etc. |
direction | An additional parameter: ago or the empty string. |
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:
It will return a result as displayed below:
In the second example, we will figure out the interval of 2 weeks and 10 days ago:
#Case 2: Select interval with “ago”
The result will be displayed in:
- days: as 2 weeks is equal to 14 days.
- negative value: as we used an ago direction.
#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:
It will return a result as displayed below:
#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:
You will get the following successful output:
#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:
You will get the following successful output:
#Case 6: Extracting data from an interval
We can extract the interval field values from timestamp with the EXTRACT() function:
- 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:
You will get 30 as an extracted result:
🚨 If you set a field that is not specified in the timestamp, you will get 0 as the output.
