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

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

INTERVAL YEAR TO MONTH
INTERVAL YEAR

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

CAST Operator
Databricks Docs
Vertica Docs - Note that CeloSQL does not support the INTERVAL syntax that Vertica does.

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

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

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.

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