
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!