INTERVAL

Measures the difference between two points in time.

Measures the difference between two points in time. Intervals can be positive or negative. The INTERVAL data type is SQL:2008 compliant, and supports interval qualifiers that are divided into two major subtypes:

  • Year-month: Span of years and months

  • Day-time: Span of days, hours, minutes, seconds, and fractional seconds

Intervals are represented internally as some number of microseconds and printed as up to 60 seconds, 60 minutes, 24 hours, 30 days, 12 months, and as many years as necessary. You can control the output format of interval units with SET INTERVALSTYLE and SET DATESTYLE.

Syntax

INTERVAL 'interval-literal' [ interval-qualifier ] [ (p) ]

Parameters

interval-literal : A character string that expresses an interval, conforming to this format:
[-] { quantity subtype-unit }[...] [ AGO ]

For details, see Interval literal.

interval-qualifier : Optionally specifies how to interpret and format an interval literal for output, and, optionally, sets precision. If omitted, the default is `DAY TO SECOND(6)`. For details, see Interval qualifier.
p
Specifies precision of the seconds field, where p is an integer between 0 - 6. For details, see Specifying interval precision.

Default: 6

Limits

Name Low Value High Value Resolution
INTERVAL DAY TO SECOND [(p)] -106751991 days 04:00:54.775807 +/-106751991 days 04:00:54.775807 1 microsecond
INTERVAL YEAR TO MONTH ~/ -768e15 yrs ~ 768e15 yrs 1 month