We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Date Interval Subtract — Vertica Forum

Date Interval Subtract

select extract(month from (current_date - interval '1 months'))

I'd expect that to return 09. Instead it returns 10. current_date = 2018-10-31 when this was executed.

Postgresql returns 10 as I expected.

Is this a bug or expected behavior?

Comments

  • marcothesanemarcothesane - Select Field - Administrator

    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

  • marcothesanemarcothesane - Select Field - Administrator

    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
    

Leave a Comment

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