Exclude days from a TIMESERIES
Moderator: NorbertKrupa
Exclude days from a TIMESERIES
Is there a way to exclude days like national holidays and company holidays from a TIMESERIES calculation?
Thank you!
Joshua
Joshua
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Exclude days from a TIMESERIES
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:
Here is simple TIMESERIES calculation:
Now I can exclude the days in my HOLIDAYS table from the above result set with the MINUS operator:
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)
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)
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.