Add or subtract days to timestamp

That works, thank you, Navin! 

I wish there is a built-in function ADD_DAYS in Vertica, especially when there is ADD_MONTHS. I'd make a feature request.

Hi,
I would like to calculate working days between two dates. This also should exclude official holidays. Is there a function in vertica ? If not how to implement this ?
Thanks
Jayvertica

Hi Jay,

Defining a function like this is a bit tricky, this can be done using the UDF feature in Vertica.
The usage of SELECT,FROM,AGGREGTES,ANALYTIC Functions is restricted while create SQL function with the current releases.

A possible workaround for this

You need to have to temporary tables / Views
1. Bank_holidays
2. Weekend_holidays

For weekend Holidays table you can use this

create local temporary table weekend_holidays  on commit preserve rows as
(SELECT * FROM(SELECTts::DATE
calendar_date,year(ts::DATE)    
 year_of_calendar,month(ts::DATE)    
 month_of_year,
 dayofweek_iso(ts::DATE) day_of_week
FROM (SELECT '01-01-2013'::TIMESTAMP as tmUNIONSELECT '12-31-2013'::TIMESTAMP as tm) as t TIMESERIES ts as '1 Day' OVER (ORDER BY tm)) sub1where sub1.day_of_week = 7);

Once this table is created, you can create another table Bank_holidays according to country required for.

CREATE TABLE bank_holidays
(   
holiday_date date,   
holiday_description varchar(50),   
holiday_year int,   
holiday_day_of_week int DEFAULT dayofweek_iso(bank_holidays.holiday_date));

Populate this table with exact data


Then you can use both these table to query and get the working days like this

select (datediff('day',cast('01-05-2013' as date), cast('02-05-2013' as date)) - holidays) as working_days
from(select count(*) as holidays from(select calendar_date from weekend_holidays wh
where wh.calendar_date between '01-05-2013' and '02-05-2013 '
UNION
 select holiday_date from bank_holidays bh
where bh.holiday_date between  '01-05-2013' and '02-05-2013' and holiday_day_of_week <> 7) sub order by 1) sub1;

This should give you working days.


Hope this helps.

Thanks Navin. I have tested the above work around for UK bank holidays. It worked fine. However I wanted to use it in  filter condition ( Date diff <=10).  I had earlier created oracle function which I could use easily in the filter condition. I tried  a bit of UDF, but seems to be difficult task.

Hi Jay,

A possible workaround,

Create a local temporary table with the last query as follows


CREATE LOCAL TEMPORARY TABLE WORKING_DAYS_TEMP ON COMMIT PRESERVE ROWS
AS
select (datediff('day',cast('01-05-2013' as date), cast('02-05-2013' as date)) - holidays) as working_days,
current_timestamp(0) as timestamp_for_working_days_extracted from
(select count(*) as holidays from
(select calendar_date from weekend_holidays wh
where wh.calendar_date between '01-05-2013' and '02-05-2013 '
UNION
 select holiday_date from bank_holidays bh
where bh.holiday_date between  '01-05-2013' and '02-05-2013' and holiday_day_of_week <> 7) sub order by 1) sub1;

The you can use a viable to store working_days value like this

\set F_WORKING_DAYS 'select working_days from WORKING_DAYS_TEMP'

To display the working days value stored in variable

nnani=> :F_WORKING_DAYS;
 working_days
--------------
           22
(1 row)

You can use this variable in your filter condition.

Hope this helps