Converting Intervals to Numerics

[Deleted User]

Jim Knicely authored this tip.

Intervals measure the difference between two points in time. Converting an interval to a numeric is easy in Vertica because we can perform operations (i.e. division) on them!

Examples:

dbadmin=> SELECT INTERVAL '24 HOURS' / INTERVAL '1 DAY' days;
 days
------
    1
(1 row)

dbadmin=> SELECT INTERVAL '36 HOURS' / INTERVAL '1 DAY' days;
 days
------
  1.5
(1 row)

dbadmin=> SELECT INTERVAL '1 DAY' / INTERVAL '2 HOURS' hours;
 hours
-------
    12
(1 row)

dbadmin=> SELECT INTERVAL '1 YEAR' / INTERVAL '1 SECOND' seconds;
  seconds
----------
31536000
(1 row)

Have fun!