Support compound interval literals: INTERVAL 'h:mm' HOUR TO MINUTE #99611
Description
Summary
ClickHouse supports single-unit interval literals (INTERVAL n UNIT) but not compound interval qualifiers with a TO range (HOUR TO MINUTE, DAY TO SECOND, YEAR TO MONTH, etc.). These allow expressing multi-unit durations in a single literal and are supported by PostgreSQL, Oracle, and others. See the PostgreSQL documentation on interval types for reference.
Current Behavior
SELECT INTERVAL '1:30' HOUR TO MINUTE;
-- Code: 62. DB::Exception: Syntax error
SELECT INTERVAL '2-6' YEAR TO MONTH;
-- Code: 62. DB::Exception: Syntax error
SELECT INTERVAL '5 12:30' DAY TO MINUTE;
-- Code: 62. DB::Exception: Syntax errorExpected Behavior
SELECT INTERVAL '1:30' HOUR TO MINUTE; -- 1 hour 30 minutes
SELECT INTERVAL '1:30:45' HOUR TO SECOND; -- 1 hour 30 minutes 45 seconds
SELECT INTERVAL '2-6' YEAR TO MONTH; -- 2 years 6 months
SELECT INTERVAL '5 12:30' DAY TO MINUTE; -- 5 days 12 hours 30 minutes
-- Usable in date arithmetic:
SELECT toDate('2024-01-01') + INTERVAL '1:30' HOUR TO MINUTE;
-- Returns: 2024-01-01 01:30:00
SELECT toDate('2024-03-01') + INTERVAL '2-6' YEAR TO MONTH;
-- Returns: 2026-09-01Workaround
-- Decompose into separate single-unit intervals and add:
SELECT INTERVAL 1 HOUR + INTERVAL 30 MINUTE;
SELECT INTERVAL 2 YEAR + INTERVAL 6 MONTH;
SELECT INTERVAL 5 DAY + INTERVAL 12 HOUR + INTERVAL 30 MINUTE;