Vertica Quick Tip: Date Arithmetic with Intervals

This blog post was authored by Jim Knicely.

In the last Vertica Quick Tip we saw how easy date arithmetic can be. Well, it can be even easier with Intervals!

Example:

What is today’s, yesterday’s and tomorrow’s date? dbadmin=> SELECT SYSDATE Today, dbadmin-> SYSDATE - INTERVAL '1 Day' Yesterday, dbadmin-> SYSDATE + INTERVAL '1 Day' Tomorrow; Today | Yesterday | Tomorrow ----------------------------+----------------------------+---------------------------- 2018-01-22 06:57:51.462211 | 2018-01-21 06:57:51.462211 | 2018-01-23 06:57:51.462211 Again, you’re not limited to whole days! You can also easily add and subtract partial days (i.e. hours, minutes and seconds) using intervals! dbadmin=> SELECT SYSDATE Today, dbadmin-> SYSDATE + INTERVAL '5 Hours' Today_Plus_5_hrs, dbadmin-> SYSDATE + INTERVAL '5 Minutes' Today_Plus_5_mins, dbadmin-> SYSDATE + INTERVAL '5 Seconds' Today_Plus_5_secs; Today | Today_Plus_5_hrs | Today_Plus_5_mins | Today_Plus_5_secs ----------------------------+----------------------------+----------------------------+---------------------------- 2018-01-22 06:59:56.151314 | 2018-01-22 06:59:56.151314 | 2018-01-22 07:04:56.151314 | 2018-01-22 07:00:01.151314 (1 row) So we’ve learned that we can add 5 seconds to a date using the computed value 5/24/60/60 or as the INTERVAL ‘5 Seconds’.

Which option performs best? Let’s find out! dbadmin=> SELECT COUNT(*) FROM big_date_table; COUNT ------------ 1000000000 (1 row) dbadmin=> \timing Timing is on. dbadmin=> SELECT COUNT(*) FROM big_date_table WHERE the_date_as_date + 5/24/60/60 = '2016-01-21 00:00:05'; COUNT -------- 330112 (1 row) Time: First fetch (1 row): 15082.918 ms. All rows formatted: 15082.988 ms That took about 15 seconds. How about if we precompute 5/24/60/60? dbadmin=> SELECT COUNT(*) FROM big_date_table WHERE the_date_as_date + 0.000057870370370370370277777777777777783333333333333333 = '2016-01-21 00:00:05'; COUNT -------- 330112 (1 row) Time: First fetch (1 row): 12655.483 ms. All rows formatted: 12655.530 ms That took about 13 seconds. Not much better. How about an Interval? dbadmin=> SELECT COUNT(*) FROM big_date_table WHERE the_date_as_date + INTERVAL '5 Seconds' = '2016-01-21 00:00:05'; COUNT -------- 330112 (1 row) Time: First fetch (1 row): 5300.209 ms. All rows formatted: 5300.258 ms Wow. Obviously the Interval option is the clear winner!

Have Fun!