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?