Measuring Months With INTERVAL


In queries using INTERVAL with months, it may be beneficial to review the behavior to achieve the desired result.

In a recent StackOverflow question, a user reported seeing unexpected behavior when using INTERVAL '1 MONTH' in queries. For example, in the following cases, one month is added to February, containing 29 days, and March, containing 31 days:

A user might expect the behavior of the INTERVAL to be a smart month, in which a full month is calculated. However, this is not the case:

As a post on the community forum describes, this behavior is due to Vertica being modeled on SQL 2008 standards. In order to achieve a smart month, the INTERVALYM (INTERVAL YEAR TO MONTH) subtype should be used:

About the author / 

Norbert Krupa

Norbert is the founder of and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user's employer.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.


This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: