Add or subtract days to timestamp

May_1

We need to get 1-day, 7-day and 30-day averages, so we need to calculate the time stamp of 1, 7 or 30 days prior to a given timestamp. There is a function ADD_MONTHS, so I was looking for something like ADD_DAYS but did not find it. What function can be used for this purpose?

Navin_C

#May,

I think this can help you

A User defined SQL function

CREATE FUNCTION ADD_DAYS(x VARCHAR, y INT) RETURN TIMESTAMP
AS BEGIN
RETURN ( CAST (x AS TIMESTAMP) + y );
END;


Usage example :

nnani=> select ADD_DAYS('2013-01-20',1);
      ADD_DAYS
---------------------
 2013-01-21 00:00:00
(1 row)

nnani=> select ADD_DAYS('2013-01-20',60);
      ADD_DAYS
---------------------
 2013-03-21 00:00:00
(1 row)

 

Let me know, if this matches your requirement

Hope this helps.

May_1

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.

jay_1

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

Navin_C

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.

jay_1

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.

jay_1

Oracle Function :
create or replace
FUNCTION f_working_days (p_start_date DATE, p_end_date DATE)
   RETURN NUMBER
IS
   v_holidays     NUMBER;
   v_start_date   DATE   := TRUNC (p_start_date);
   v_end_date     DATE   := TRUNC (p_end_date);
BEGIN
   IF v_end_date >= v_start_date
   THEN
      SELECT COUNT (holiday)
        INTO v_holidays
        FROM HOLIDAY_DAYS
       WHERE holiday BETWEEN v_start_date AND v_end_date;

      RETURN   GREATEST (NEXT_DAY (v_start_date, 'MON') - v_start_date - 2, 0)
             +   (  (  NEXT_DAY (v_end_date, 'MON')
                     - NEXT_DAY (v_start_date, 'MON')
                    )
                  / 7
                 )
               * 5
             - GREATEST (NEXT_DAY (v_end_date, 'MON') - v_end_date - 3, 0)
             - v_holidays;
   ELSE
      RETURN NULL;
   END IF;
END f_working_days;

Navin_C

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