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?
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:
) AS (
, DATE '2018-03-31'
, DATE '2018-02-27'
, DATE '2018-03-30'
last_of_feb - INTERVAL '1 MONTH'
, last_of_mar - INTERVAL '1 MONTH'
, second_last_of_feb - INTERVAL '1 MONTH'
, second_last_of_mar - INTERVAL '1 MONTH'
, ADD_MONTHS(last_of_feb , -1 )
, ADD_MONTHS(last_of_mar , -1 )
, ADD_MONTHS(second_last_of_feb , -1 )
, ADD_MONTHS(second_last_of_mar , -1 )
You'll see that the ADD_MONTHS() expressions behave as you'd expect ...
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