Date Interval Subtract

mritsema

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?

marcothesane

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

marcothesane

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