In this section, we are going to understand the working of the PostgreSQL Interval data type, and we also see examples of the Interval data type. And mostly used Interval functions, for example, NOW(), TO_CHAR(), EXTRACT(), justify_days(), justify_hours(), justify_interval(). The Input and output format for PostgreSQL interval values.
What is PostgreSQL Interval Data Type?
In PostgreSQL, the Interval is another type of data type used to store and deploy Time in years, months, days, hours, minutes, seconds, etc. And the months and days values are integers values, whereas the second’s field can be the fractions values.
PostgreSQL interval data type value involves 16 bytes storage size, which helps to store a period with the acceptable range from –178000000 years to 178000000 years.
Note: The number of fraction digits taken in the second field is known as the precision p.
Syntax of PostgreSQL Interval data type
The Syntax of PostgreSQL Interval data type is as follows:
@ interval [ fields ] [ (p) ] In the above syntax, we have the following parameters:
| Parameter | Description |
|---|---|
| fields | The field parameter is used to show the Time. |
| p | P is used to display precision value. |
| @ | We can ignore the @ parameter as it is an optional parameter. |
Let us see a sample example for our better understanding that how we can write the Interval values with @ parameter and without @ parameter:
@interval '6 months before';
@interval '2 hours 30 minutes'; OR
interval '6 months before';
interval '2 hours 30 minutes'; Note: We can use both the above statements as they correspond to each other. Furthermore, an interval value can have an elective precision value p with the allowed range is started from 0 to 6.
Example of PostgreSQL Interval data type
Let us see one sample examples to understand how the PostgreSQL Interval data type works.
In the below example, we will try to find the time of 2 hours 30 minutes before, at the current time of last year; we will use the following commands:
SELECT
now(),
now() - INTERVAL '6 months 2 hours 30 minutes'
AS "2 hours 30 minutes before last year"; Output
After executing the above command, we will get the below output, which displays 2 hours 30 minutes before last year:
We have seen the primary example of interval data type and understand the working of it. Now, we will see the Input and output format of interval values.
Firstly, we will understand the PostgreSQL interval value for the input format:
PostgreSQL Interval Input Format
In PostgreSQL, we have the following verbose syntax, which helps us to write the interval values:
quantity unit [quantity unit...] [direction] We have the below parameters, which are used in the above syntax:
| Parameter | Description |
|---|---|
| quantity | A quantity is a number, which also accepts signs like + or – |
| unit | The unit can be any millennium, century, decade, year, month, week, day, hour, minute, second, millisecond, microsecond, or abbreviation can be the following y, m, d, etc. and the plural forms can be the months, days, etc. |
| direction | The direction parameter can be ago or the empty string. |
Note: The above syntax is also used for the interval output format and known as postgres_verbose.
In the below example, we will display some interval values where we use the verbose syntax:
INTERVAL '1 year 5 months 5 days';
INTERVAL '1 weeks ago'; The ISO 8601 Interval Format
Besides the verbose syntax above, to write the interval values with the help of ISO 8601-time intervals, PostgreSQL provides us the two ways, which are as follows:
- Designators format
- Alternative format
The designators format for ISO 8601 is as follows:
P quantity unit [ quantity unit ...] [ T [ quantity unit ...]] In the above format, the interval value essential begins with the letter P, and the letter T is used to define the time-of-day unit.
The below table displays the ISO 8601 interval unit abbreviations:
| Abbreviation | Description |
|---|---|
| Y | Years |
| M | Months (for the date part) |
| W | Weeks |
| D | Days |
| H | Hours |
| M | Minutes (for the time part) |
| S | Seconds |
Note: The M can be months or minutes based on whether it looks before or after the letter T.
Let us see an example of ISO 8601 designators format for our better understanding:
The ISO 8601 designators format can be written for the interval of 5 years 4 months 3 days 2 hours 1 minutes 1 second:
P5Y4M3DT2H1M1S And the alternative form of ISO 8601 is as shown below:
P [ years-months-days ] [ T hours:minutes:seconds ] And it also begins with the letter P and the letter T, which splits the time and date parts of the interval value.
For example, the ISO 8601 alternative form can be written for 5 years 4 months 3 days 2 hours 1 minute 1 second, as shown below:
P0005-04-03T02:01:01 PostgreSQL Interval Output Format
The PostgreSQL interval output format of interval values can be set with the help of the SET intervalstyle command, as we can see in the below example:
SET intervalstyle = 'iso_8601'; PostgreSQL contains four different output formats, such as:
- iso_8601
- postgres_verbose
- Postgres
- sql standard
Note: To format the interval values, PostgreSQL uses the Postgres style by default.
Let us see an example of our better understanding:
The below command is used to display the 5 years 4 months 3 days 2 hours 1minute 1 second in the four different output formats:
For ISO_8601 Output Format
In the below command, we will SET the intervalstyle as iso_8601 for the above mentioned interval value:
SET intervalstyle = 'iso_8601';
SELECT
INTERVAL '5 years 4 months 3 days 2 hours 1 minute 1 second'; Output
After executing the above command, we will get the below result, which represents the iso_8601 interval output format:
For postgres_verbose output format
In the below command, we will SET the intervalstyle as postgres_verbose for the above-mentioned interval value:
SET intervalstyle = 'postgres_verbose';
SELECT
INTERVAL '5 years 4 months 3 days 2 hours 1 minutes 1 second';Output
We will get the below result after implementing the above command, which shows the postgres_verbose interval output format:
For Postgres output format
In the below command, we will SET the intervalstyle as Postgres for the above- mentioned interval value:
SET intervalstyle = 'postgres';
SELECT
INTERVAL '5 years 4 months 3 days 2 hours 1 minutes 1 second'; Output
We will get the below result after successfully executing the above command, which shows the Postgres interval output format:
For Sql_standard Output Format
In the below command, we will SET the intervalstyle as sql_standard for the above- mentioned interval value:
SET intervalstyle = 'sql_standard';
SELECT
INTERVAL '5 years 4 months 3 days 2 hours 1 minutes 1 second'; Output
We will get the below result after successfully executing the above command, which represents the sql_standard interval output format:
Modifying the PostgreSQL interval to string
We can use the TO_CHAR() function to modify an interval value to a string.
The Syntax for modifying the PostgreSQL interval to string
TO_CHAR(interval,format) The TO_CHAR() function takes the first statement as an interval value and another one as the format and retrieves a string, displaying the interval in the particular format.
In the below example, we will use the TO_CHAR() function, which converts the PostgreSQL interval into the string value:
SELECT
TO_CHAR(
INTERVAL '15h 15m 15s','HH24:MI:SS'
); Output
After successfully executing the above command, we will get the below result:
PostgreSQL Interval Related Operators and Functions
Interval operators
We can use the following +, -, *, etc. arithmetic operator to get the interval values.
Let us see the below example for our better understanding:
In the following command, we will use the arithmetic operator (+) with the help of PostgreSQL interval data type:
SELECT
INTERVAL '1h 50m' + INTERVAL '5m'; Output
We will get the below result on implementing the above command, as shown below:
In the following command, we will use the arithmetic operator (-) with the help of PostgreSQL interval data type:
SELECT
INTERVAL '3h 50m' - INTERVAL '30m'; Output
On implementing the above command, we will get the below result:
In the following command, we will use the arithmetic operator (*) with the help of PostgreSQL interval data type:
SELECT
400 * INTERVAL '5 minute'; Output
On implementing the above command, we will get the below result:
Extracting data from a PostgreSQL interval
We can use the EXTRACT() function to extract the fields from an interval value, For example, year, month, date, etc.
The Syntax for Extracting data from a PostgreSQL interval
EXTRACT(field FROM interval) In the above syntax, we can use the year, month, date, hour, minutes, etc., in the field parameter.
The extract function returns a value of type double-precision if we want to extract from the interval.
In the following example, we will use the EXTRACT() function to retrieve the double-precision value.
SELECT
EXTRACT (MINUTE
FROM
INTERVAL '2 hours 30 minutes'
); Output
After successfully executing the above command, we will get the below output, which displays the date part for the double- precision values as shown below:
Adjusting PostgreSQL interval values
We have two functions, justify_days and justify_hours in PostgreSQL, which permits us to modify the interval of 24-hour consider as one day and the interval of 30-day consider as one month.
Let us see an example to understand how we are adjusting the PostgreSQL interval values:
In the following command, we will use the justify_days and justify_hours function:
SELECT
justify_days(INTERVAL '30 days'),
justify_hours(INTERVAL '24 hours'); Output
After executing the above command, we will get the following result, which displays the interval of 30-day as one month and the interval of 24-hour as one day:
Besides that, the justify_interval function regulates the interval values with the help of justify_days and justify_hours with added sign modifications:
In the following example, we will use the justifiy_interval function with the SELECT command:
SELECT
justify_interval(interval '6 months -1 hour'); Output
We will get the following output after implementing the above command:
Overview
In the PostgreSQL Interval data type section, we have learned the following topics:
- The PostgreSQL Interval data type is used to store and deploy a period in years, months, days, hours, minutes, seconds.
- We used different Interval functions, for example, NOW(), TO_CHAR(), to enhance interval values from the particular table.
- The PostgreSQL interval values have both input and output interval format.
- We can adjust the PostgreSQL interval values with the help of justify_days(), justify_hours, justify_interval() functions.
- We can use the EXTRACT() function to extract the interval field values.
Leave a Reply