The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Add or subtract days to timestamp

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?

Comments

  • #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.

  • 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.
  • 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;

  • 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




Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.