Casting with Intervals
You can use CAST
to convert strings to intervals, and vice versa.
String to Interval
You cast a string to an interval as follows:
CAST( [ INTERVAL[(p)] ] [-] ] interval‑literal AS INTERVAL[(p)] interval‑qualifier )
For example:
=> SELECT CAST('3700 sec' AS INTERVAL); ?column? ---------- 01:01:40
You can cast intervals within day-time or the year-month subtypes but not between them:
=> SELECT CAST(INTERVAL '4440' MINUTE as INTERVAL); ?column? ---------- 3 days 2 hours => SELECT CAST(INTERVAL -'01:15' as INTERVAL MINUTE); ?column? ---------- -75 mins
Interval to String
You cast an interval to a string as follows:
CAST( (SELECT interval ) AS VARCHAR[(n)] )
For example:
=> SELECT CONCAT( 'Tomorrow at this time: ', CAST((SELECT INTERVAL '24 hours') + CURRENT_TIMESTAMP(0) AS VARCHAR)); CONCAT ----------------------------------------------- Tomorrow at this time: 2016-08-17 08:41:23-04 (1 row)