Interval Literals

Interval literals denote fixed spans of time and can be written in the ANSI or multi-unit syntax.

Interval literals are only supported for the year-month and day-time interval types. For calendar intervals of the LITERAL SQL type (which corresponds to Spark CalendarIntervalType or the Arrow Interval(MonthDayNano) type), you can use the make_interval function to construct its values.

ANSI Interval Syntax

The ANSI SQL standard defines interval literals in the following form:

INTERVAL [+|-]interval-string interval-qualifier

interval-qualifier can be a single field or in the field-to-field form:

fieldfield TO field

field is case-insensitive, and can be one of YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. Note that in the field-to-field form, the first field must be of a coarser granularity than the second field. The valid field pairs are listed in the interval format tables later in this section.

interval-string is an interval literal string that corresponds to either the year-month or day-time interval type:

'[+|-](year-month-literal|day-time-literal)'

year-month-literal takes the following forms:

y[-m]m

y and m are the number of years and months, respectively.

day-time-literal takes the following forms:

d[ H[:M[:S[.f]]]]H[:M[:S[.f]]]M[:S[.f]]S[.f]

d, H, M, S, and f are the number of days, hours, minutes, seconds, and fractional seconds, respectively.

Here are the supported formats for year-month interval literals.

Qualifier	Signed String	Example
YEAR	[+|-]'[+|-]y'	INTERVAL -'1999' YEAR
YEAR TO MONTH	[+|-]'[+|-]y-m'	INTERVAL '-1999-11' YEAR TO MONTH
MONTH	[+|-]'[+|-]m'	INTERVAL '24' MONTH

Here are the supported formats for day-time interval literals.

Qualifier	Signed String	Example
DAY	[+|-]'[+|-]d'	INTERVAL -'365' DAY
DAY TO HOUR	[+|-]'[+|-]d H'	INTERVAL '-10 05' DAY TO HOUR
DAY TO MINUTE	[+|-]'[+|-]d H:M'	INTERVAL '100 10:30' DAY TO MINUTE
DAY TO SECOND	[+|-]'[+|-]d H:M:S[.f]'	INTERVAL '100 10:30:40.999999' DAY TO SECOND
HOUR	[+|-]'[+|-]H'	INTERVAL '123' HOUR
HOUR TO MINUTE	[+|-]'[+|-]H:M'	INTERVAL -'-15:45' HOUR TO MINUTE
HOUR TO SECOND	[+|-]'[+|-]H:M:S[.f]'	INTERVAL '123:10:59' HOUR TO SECOND
MINUTE	[+|-]'[+|-]M'	INTERVAL '5000' MINUTE
MINUTE TO SECOND	[+|-]'[+|-]M:S[.f]'	INTERVAL '2000:02.002' MINUTE TO SECOND
SECOND	[+|-]'[+|-]S[.f]'	INTERVAL '2000.000002' SECOND

ANSI Interval Examples

SELECT INTERVAL '5-6' YEAR TO MONTH AS col;
-- +----------------------------+
-- |col                         |
-- +----------------------------+
-- |INTERVAL '5-6' YEAR TO MONTH|
-- +----------------------------+

SELECT INTERVAL -'12 23:45:59.888888' DAY TO SECOND AS col;
-- +--------------------------------------------+
-- |col                                         |
-- +--------------------------------------------+
-- |INTERVAL '-12 23:45:59.888888' DAY TO SECOND|
-- +--------------------------------------------+

Multi-unit Interval Syntax

INTERVAL interval-value interval-unit( interval-value interval-unit)*INTERVAL 'interval-value interval-unit( interval-value interval-unit)*'

interval-value is a signed integer or its string representation.

[+|-]number'[+|-]number'

interval-unit is a case-insensitive interval unit:

YEAR[S]MONTH[S]WEEK[S]DAY[S]HOUR[S]MINUTE[S]SECOND[S]MILLISECOND[S]MICROSECOND[S]

Mix of the YEAR[S] or MONTH[S] interval units with other units is not allowed.

INFO

Although Sail supports the YEAR[S] and MONTH[S] interval units in the multi-unit syntax, the Spark client is unable to convert these from Arrow when invoking .collect() or .toPandas().

Multi-unit Interval Examples

SELECT INTERVAL 3 WEEK AS col;
-- +-----------------+
-- |col              |
-- +-----------------+
-- |INTERVAL '21' DAY|
-- +-----------------+

SELECT INTERVAL -2 WEEKS '3' DAYS AS col;
-- +------------------+
-- |col               |
-- +------------------+
-- |INTERVAL '-11' DAY|
-- +------------------+

SELECT INTERVAL '3 DAYS 50 SECONDS' AS col;
-- +-----------------------------------+
-- |col                                |
-- +-----------------------------------+
-- |INTERVAL '3 00:00:50' DAY TO SECOND|
-- +-----------------------------------+

SELECT INTERVAL 3 WEEK 4 DAYS 5 HOUR 6 MINUTES 7 SECOND 8 MILLISECOND 9 MICROSECONDS AS col;
-- +-------------------------------------------+
-- |col                                        |
-- +-------------------------------------------+
-- |INTERVAL '25 05:06:07.008009' DAY TO SECOND|
-- +-------------------------------------------+