Date Arithmetic with Intervals: Vertica Tip

[Deleted User] · January 2018

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 are today’s, yesterday’s, and tomorrow’s dates?

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!