Interval Type
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.
Here is the description of each parameter that is used in the above syntax:
Parameters | Description |
quantity | A specified number (can be applied with a - sign to separate each quantity). |
unit |
|
direction | An additional parameter: ago or the empty string. |
This example demonstrates how to express an interval of 5 years, 4 months, 3 days, 5 hours, 10 minutes, and 25 seconds using the INTERVAL keyword.
It will return the result as displayed below:
In the second example, we will figure out the interval at 2 weeks and 10 days ago:
The result will be displayed in:
- days as 2 weeks is equal to 14 days.
- negative values, as we used an ago direction.
By using the following command, we will have an interval of 3600 months ago but in years unit. The (-) symbol is added before the quantity:
It will return the result as displayed below:
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:
In the previous example, we selected an interval by years and months with a specified value. In this example, we will choose an interval by day and time:
You will get the following successful output:
We can extract the interval field values from the 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 the specified timestamp below:
You will get 30 as an extracted result:
If you query a field that is not specified in the timestamp, you will get 0 as output.