Skip to main content

INTERVAL types

Data type that represents intervals of time on either a scale of months or seconds.

The following interval types are supported in CeloSQL:

Year-Month intervals:

Interval Type

Description

INTERVAL YEAR

Represents an interval measured in years

INTERVAL YEAR TO MONTH

Represents an interval measured in years and months

INTERVAL MONTH

Represents an interval measured in months

Day-Time intervals:

Interval Type

Description

INTERVAL DAY

Represents an interval measured in days

INTERVAL DAY TO HOUR

Represents an interval measured in days and hours

INTERVAL DAY TO MINUTE

Represents an interval measured in days, hours, and minutes

INTERVAL DAY TO SECOND

Represents an interval measured in days, hours, minutes, and seconds

INTERVAL HOUR

Represents an interval measured in hours

INTERVAL HOUR TO MINUTE

Represents an interval measured in hours and minutes

INTERVAL HOUR TO SECOND

Represents an interval measured in hours, minutes, and seconds

INTERVAL MINUTE

Represents an interval measured in minutes

INTERVAL MINUTE TO SECOND

Represents an interval measured in minutes and seconds

INTERVAL SECOND

Represents an interval measured in seconds

Syntax

INTERVAL YEAR TO MONTH
INTERVAL YEAR

The accepted string formats for intervals will vary from engine to engine. References for differences:

Note: CeloSQL day-time intervals do not support precision.

Negative Intervals

Negative intervals are represented differently on Vertica and Databricks:

A - prefix for an interval will negate the entire interval on Databricks, but only the first value on Vertica. Example:

SELECT INTERVAL '-1-2' YEAR TO MONTH;
-- DBX: -1 years + -2 months
-- Vertica: -1 years + 2 months

Limits

A year-month interval has a maximal limit of 178,956,970 years and 11 months.

A day-time interval has a maximal limit of 106,751,991 days, 23 hours, 59 minutes, and 59.999999 seconds.

Literals

INTERVAL '1-2' YEAR TO MONTH
INTERVAL '3' YEAR

INTERVAL '12 12:34:56.789123' DAY TO SECOND
INTERVAL '12:34' HOUR TO MINUTE
INTERVAL '12' MINUTE