PostgreSQL Interval

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:

In the above syntax, we have the following parameters:

ParameterDescription
fieldsThe field parameter is used to show the Time.
pP 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:

OR

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:

Output

After executing the above command, we will get the below output, which displays 2 hours 30 minutes before last year:

PostgreSQL Interval

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:

We have the below parameters, which are used in the above syntax:

ParameterDescription
quantityA quantity is a number, which also accepts signs like + or -
unitThe 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.
directionThe 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:

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:

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:

AbbreviationDescription
YYears
MMonths (for the date part)
WWeeks
DDays
HHours
MMinutes (for the time part)
SSeconds

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:

And the alternative form of ISO 8601 is as shown below:

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:

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:

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:

Output

After executing the above command, we will get the below result, which represents the iso_8601 interval output format:

PostgreSQL Interval

For postgres_verbose output format

In the below command, we will SET the intervalstyle as postgres_verbose for the above-mentioned interval value:

Output

We will get the below result after implementing the above command, which shows the postgres_verbose interval output format:

PostgreSQL Interval

For Postgres output format

In the below command, we will SET the intervalstyle as Postgres for the above- mentioned interval value:

Output

We will get the below result after successfully executing the above command, which shows the Postgres interval output format:

PostgreSQL Interval

For Sql_standard Output Format

In the below command, we will SET the intervalstyle as sql_standard for the above- mentioned interval value:

Output

We will get the below result after successfully executing the above command, which represents the sql_standard interval output format:

PostgreSQL Interval

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

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:

Output

After successfully executing the above command, we will get the below result:

PostgreSQL Interval

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:

Output

We will get the below result on implementing the above command, as shown below:

PostgreSQL Interval

In the following command, we will use the arithmetic operator (-) with the help of PostgreSQL interval data type:

Output

On implementing the above command, we will get the below result:

PostgreSQL Interval

In the following command, we will use the arithmetic operator (*) with the help of PostgreSQL interval data type:

Output

On implementing the above command, we will get the below result:

PostgreSQL Interval

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

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.

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:

PostgreSQL Interval

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:

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:

PostgreSQL Interval

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:

Output

We will get the following output after implementing the above command:

PostgreSQL Interval

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.

Next TopicPostgreSQL UUID




Latest Courses