TutorialsArena

PostgreSQL: Working with the INTERVAL Data Type

Store and manipulate time periods in PostgreSQL using the INTERVAL data type. This guide covers syntax, input/output formats, and related functions.

Working with PostgreSQL's `interval` Data Type

Introduction

PostgreSQL's `interval` data type is used to store and manipulate time periods. This article explains the `interval` type, its syntax, various input/output formats, and related functions and operators.

PostgreSQL `interval` Data Type

The `interval` type represents a time period, including years, months, days, hours, minutes, and seconds. Months and days are stored as integers, while seconds can include fractional parts. An `interval` value occupies 16 bytes of storage.

The valid range is approximately -178,000,000 years to +178,000,000 years.

`interval` Syntax

`interval` Syntax

interval 'value' [ (p) ];

The `value` is a string representing the time period. The optional `p` specifies the precision (number of fractional digits for seconds).

`interval` Input Formats

Verbose Syntax

PostgreSQL's verbose syntax for `interval` values is:

Verbose Syntax

quantity unit [quantity unit...] [direction]

For example: INTERVAL '1 year 2 months 3 days' or INTERVAL '1 week ago'

ISO 8601 Format

PostgreSQL also supports the ISO 8601 standard for representing intervals. There are two variations:

ISO 8601 Designators Format

P quantity unit [quantity unit...] [T [quantity unit...]]

Example: `P2Y6M3DT1H30M` (2 years, 6 months, 3 days, 1 hour, 30 minutes)

ISO 8601 Alternative Format

P [years-months-days] [T hours:minutes:seconds]

Example: `P2-6-3T1:30:00`

Abbreviation Description
Y Years
M Months (date part) or Minutes (time part)
W Weeks
D Days
H Hours
S Seconds

`interval` Output Formats

PostgreSQL offers several output formats for `interval` values, controllable using `SET intervalstyle`:

  • iso_8601
  • postgres_verbose
  • postgres (default)
  • sql_standard

(Examples showing the different output formats for a sample interval would be included here.)

Converting `interval` to String using `TO_CHAR()`

`TO_CHAR()` Syntax

TO_CHAR(interval, format)

Working with PostgreSQL Intervals: Extraction and Adjustment

Introduction

This section builds upon the previous explanation of PostgreSQL's `interval` data type, demonstrating how to extract specific components from an interval and how to adjust or normalize interval values using built-in functions.

Extracting Data from an Interval using `EXTRACT()`

The `EXTRACT()` function allows you to retrieve individual components (years, months, days, hours, minutes, seconds) from an interval value.

`EXTRACT()` Syntax

`EXTRACT()` Syntax

EXTRACT(field FROM interval)

field can be `year`, `month`, `day`, `hour`, `minute`, `second`, etc.

Example: Extracting Minutes

`EXTRACT()` Example

SELECT EXTRACT(MINUTE FROM INTERVAL '2 hours 30 minutes');
Example Output

30

The `EXTRACT()` function returns a double-precision floating-point number.

Adjusting Interval Values

PostgreSQL provides functions to normalize intervals. `justify_days()` treats 24 hours as one day, and `justify_months()` treats 30 days as one month. `justify_interval()` combines these adjustments.

Example: Using `justify_days()` and `justify_hours()`

`justify_days()` and `justify_hours()` Example

SELECT justify_days(INTERVAL '30 days'), justify_hours(INTERVAL '24 hours');
Example Output

1 mon
1 day

Example: Using `justify_interval()`

`justify_interval()` Example

SELECT justify_interval(interval '6 months -1 hour');
Example Output

5 mons -1 hour

Conclusion

PostgreSQL's `interval` data type offers powerful features for working with time periods. The `EXTRACT()` function allows you to easily access individual components, while the `justify_days()`, `justify_hours()`, and `justify_interval()` functions help normalize interval values for more consistent representation.