Exclude days from a TIMESERIES
Posted: Fri Mar 04, 2016 2:45 pm
Is there a way to exclude days like national holidays and company holidays from a TIMESERIES calculation?
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)