Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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.