[FLINK-24426] Add support for cast from a string to an INTERVAL

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Marios Trivyzas
• Created: 30/Sep/21 10:36
• Updated: 30/Sep/21 11:18

Description

PostgreSQL allows casts like:

 

postgres=# select '2 YEARS 5 month 3 HOURS 10 days 2 mins 6 seconds'::interval;
interval
---------------------------------
 2 years 5 mons 10 days 03:02:06
(1 row)

postgres=# select '+1 10:17:36.789'::interval;
{{ interval }}
--------------------
{{ 1 day 10:17:36.789}}
(1 row)

 

We should at least support the latter which is the standard string representation of an interval, when the inverse cast from INTERVAL to string is used.

 

Oracle on the other hand uses dedicated functions for such conversion: to_dsinterval() but also supports:

select CAST('+02-01' AS INTERVAL YEAR TO MONTH) FROM DUAL;

+02-01