Exclude days from a TIMESERIES

Moderator: NorbertKrupa

Post Reply
User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

Exclude days from a TIMESERIES

Post by Josh » Fri Mar 04, 2016 2:45 pm

Is there a way to exclude days like national holidays and company holidays from a TIMESERIES calculation?
Thank you!
Joshua

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Exclude days from a TIMESERIES

Post by JimKnicely » Fri Mar 04, 2016 3:31 pm

Hi,

I do not think there is a built-in option (parameter) to exclude holidays from a TIMESERIES.

One option might be to have a HOLIDAY table with days you wish to exclude.

Example:

Code: Select all

dbadmin=> SELECT * FROM holidays;
holiday_date
--------------
2016-12-25
2017-01-01
(2 rows)
Here is simple TIMESERIES calculation:

Code: Select all

dbadmin=> SELECT ts::date
dbadmin->   FROM (SELECT '24-dec-2016'::TIMESTAMP as tm
dbadmin(>          UNION
dbadmin(>         SELECT '02-jan-2017'::TIMESTAMP as tm) as t
dbadmin-> TIMESERIES ts as '1 DAY' OVER (ORDER BY tm);
     ts
------------
2016-12-24
2016-12-25
2016-12-26
2016-12-27
2016-12-28
2016-12-29
2016-12-30
2016-12-31
2017-01-01
2017-01-02
(10 rows)
Now I can exclude the days in my HOLIDAYS table from the above result set with the MINUS operator:

Code: Select all

dbadmin=> SELECT ts::date
dbadmin->   FROM (SELECT '24-dec-2016'::TIMESTAMP as tm
dbadmin(>          UNION
dbadmin(>         SELECT '02-jan-2017'::TIMESTAMP as tm) as t
dbadmin-> TIMESERIES ts as '1 DAY' OVER (ORDER BY tm)
dbadmin-> MINUS
dbadmin-> SELECT holiday_date FROM holidays
dbadmin-> ORDER BY 1;
     ts
------------
2016-12-24
2016-12-26
2016-12-27
2016-12-28
2016-12-29
2016-12-30
2016-12-31
2017-01-02
(8 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica”