Date Interval Subtract

Comments

• marcothesane - Select Field - Administrator

  October 2018

  The more standardised behaviour is not adding / substracting year-to-month intervals from dates, really.

  In Vertica, if you subtract INTERVAL '1 MONTH' from a date type, the result is a timestamp - and that's probably consistent with it assuming a 30 day's difference ...

  ADD_MONTHS has the standard behaviour of ending up with last-of-month if the input is last-of-month. I suppose that's what you're after.

  See this test here:

  WITH testdt(
    last_of_feb
  , last_of_mar
  , second_last_of_feb
  , second_last_of_mar
  ) AS (
  SELECT 
    DATE '2018-02-28'
  , DATE '2018-03-31'
  , DATE '2018-02-27'
  , DATE '2018-03-30'
  )
  SELECT
    last_of_feb        - INTERVAL '1 MONTH' 
    AS last_of_jan
  , last_of_mar        - INTERVAL '1 MONTH' 
    AS last_of_feb
  , second_last_of_feb - INTERVAL '1 MONTH' 
    AS second_last_of_jan
  , second_last_of_mar - INTERVAL '1 MONTH' 
    AS second_last_of_feb
  , ADD_MONTHS(last_of_feb        , -1 )    
    AS last_of_jan_amth
  , ADD_MONTHS(last_of_mar        , -1 )    
    AS last_of_feb_amth
  , ADD_MONTHS(second_last_of_feb , -1 )    
    AS second_last_of_jan_amth
  , ADD_MONTHS(second_last_of_mar , -1 )    
    AS second_last_of_feb_amth
  FROM testdt;
  

  You'll see that the ADD_MONTHS() expressions behave as you'd expect ...

  Marco

  0
• marcothesane - Select Field - Administrator

  November 2018

  The Vertica documentation on Date/Time operators is here:
  https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Operators/DateTimeOperators.htm?TocPath=SQL%20Reference%20Manual|SQL%20Language%20Elements|Operators|_____5

  And I might add the result of the above script:

  gessnerm@linux-pgkq:~/1/aaa> vsql -x -f /tmp/b.sql
  -[ RECORD 1 ]-----------+--------------------
  last_of_jan             | 2018-01-29 00:00:00
  last_of_feb             | 2018-03-01 00:00:00
  second_last_of_jan      | 2018-01-28 00:00:00
  second_last_of_feb      | 2018-02-28 00:00:00
  last_of_jan_amth        | 2018-01-31
  last_of_feb_amth        | 2018-02-28
  second_last_of_jan_amth | 2018-01-27
  second_last_of_feb_amth | 2018-02-28
  

  0