INTERVAL MONTH
Recently we started using Looker against our Vertica cluster. And in one of queries generated by the tool it wanted to add INTERVAL 1 MONTH to a date parameter so that it could effectively do a BETWEEN query and find all of the values in the selected month. However, when I compared the results of this Look against a report generated in Jasper, the Look numbers were higher. As I dug into the issue I discovered that Vertica treats a '1 MONTH' interval as 30 DAYS regardless of which date you are adding the interval to.
So, the query select DATE '2013-02-01' + INTERVAL '1 MONTH' resolves to '2013-03-03' and select DATE '2013-05-01' + INTERVAL '1 MONTH' resolves to '2013-05-31'. Running these same types of queries in MySQL and Oracle results in the expected results of '2013-03-01' and '2013-06-01'.
Has anyone else encountered this issue?
Comments
In case somebody wonders what the solution is, here is the response from Vertica Support. The next question is can we make "year to month" the default behavior for our database.
In some older version we made Vertica SQL 2008 compliant. Part of that was to change the default qualifier for INTERVAL to "day to second", which as you saw results in a static 30 day month vs. a smart month. If you pass in a "year to month" qualifier you will get the expected results. The key to getting the smart month vs. the internally manufactured 30 days static month is to provide the alternate qualifier. See my annotated tests below.
# default as you saw
=> select date '2013-02-01' + INTERVAL '1 MONTH';
?column?
---------------------
2013-03-03 00:00:00
(1 row)
# passing the defualt qualifier to show it is the default => select date '2013-02-01' + INTERVAL '1 MONTH' day to second;
?column?
---------------------
2013-03-03 00:00:00
(1 row)
# using the year ot month qualifier to get smart month vs static 30 day month => select date '2013-02-01' + INTERVAL '1 MONTH' year to month;
?column?
---------------------
2013-03-01 00:00:00
(1 row)
=> select date '2013-05-01' + INTERVAL '1 MONTH' year to month;
?column?
---------------------
2013-06-01 00:00:00
(1 row)
It looks like there is no way to change the default behavior. However, there is an alias called INTERVALYM which gives the expected "smart" results.
=> select date '2013-05-01' + INTERVAL '1 MONTH';
?column?
---------------------
2013-05-31 00:00:00
(1 row)
=> select date '2013-05-01' + INTERVALYM '1 MONTH';
?column?
---------------------
2013-06-01 00:00:00
(1 row)