PostgreSQL IntervalIn 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 typeThe Syntax of PostgreSQL Interval data type is as follows: In the above syntax, we have the following parameters:
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 typeLet 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: 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 FormatIn 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:
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 FormatBesides 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:
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:
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 FormatThe 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:
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 FormatIn 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: For postgres_verbose output formatIn 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: For Postgres output formatIn 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: For Sql_standard Output FormatIn 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: Modifying the PostgreSQL interval to stringWe 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 Related Operators and FunctionsInterval 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: 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: 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: Extracting data from a PostgreSQL intervalWe 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: Adjusting PostgreSQL interval valuesWe 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: 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: OverviewIn the PostgreSQL Interval data type section, we have learned the following topics:
Next TopicPostgreSQL UUID |