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 vary depending on the target execution environment. For detailed differences in implementation, refer to:

CeloSQL casting documentation
Spark-based engine: Refer to the standard documentation for distributed SQL interval types and literals.
Vertica: Vertica Docs

Note: CeloSQL does not support the specialized INTERVAL syntax used in Vertica, and CeloSQL day-time intervals do not support precision.

Negative intervals

The representation of negative intervals differs between execution environments. When using a - prefix for an interval, the scope of the negation varies:

Spark-based engine: A - prefix negates the entire interval (all units).
Vertica: A - prefix typically negates only the first value in the interval.

Example comparison:

SELECT INTERVAL '-1-2' YEAR TO MONTH;

-- Spark-based engine: -1 years + -2 months
-- Vertica: -1 years + 2 months

Note: CeloSQL does not support the specialized INTERVAL syntax used in Vertica, and CeloSQL day-time intervals do not support precision.

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