Adding an interval of 1 calendar month to a date
I would like to add 1 calendar month to the date, ignoring the number of days in the month. those. add_month('2015-02-23')
returns 2015-03-23
and add_month('2015-05-23')
returns2015-06-23
Seems like I could use INTERVAL '1 month'
to do this, but I was surprised to find that whenever I do it it adds 30 days to my input, which is functionally the same as INTERVAL '30 days'
. Does this happen for you too? What to do instead of increasing by 1 calendar month?
Examples:
SELECT DATE('2015-04-23') + INTERVAL '1 month'
returns 2015-05-23
while
SELECT DATE('2015-05-23') + INTERVAL '1 month'
returns 2015-06-22
!
source to share
This is because Vertica is modeled after SQL 2008 , which 1 MONTH
is static for 30 days, not a smart month. "
dbadmin=> SELECT INTERVAL '1 MONTH'; ?column? ---------- 30 (1 row)
To get the desired behavior, you should use INTERVALYM
:
dbadmin=> SELECT INTERVALYM '1 MONTH';
?column?
----------
0-1
(1 row)
dbadmin=> SELECT DATE('2015-05-23') + INTERVALYM '1 MONTH';
?column?
---------------------
2015-06-23 00:00:00
(1 row)
source to share