Add or Subtract Months to a Date – Apache Impala

There are plenty of built-in functions available in Apache Impala that helps in analyzing the data and business logic implementations. It relieves a developer’s worries and eliminates the necessity for user-defined functions. Built-in functions aid analysts in the process of analyzing, cleansing, transforming, and modeling data in order to identify valuable information, draw conclusions, and support decision-making.

This is one of a series of articles describing how to use “date-time” functions. We’ll go through how to add and subtract a certain number of months from a given date, in this post.

MONTHS_ADD and MONTHS_SUB are the functions that adds and subtracts a specified number of months from a TIMESTAMP value. These functions deal with an actual TIMESTAMP value instead of a string that is converted to a TIMESTAMP.

Syntax:
MONTHS_ADD(TIMESTAMP startdate, INT days),
MONTHS_ADD(TIMESTAMP startdate, BIGINT days)
MONTHS_SUB(TIMESTAMP startdate, INT days),
MONTHS_SUB(TIMESTAMP startdate, BIGINT days)

Examples:

SELECT NOW() AS CurrentDate, MONTHS_ADD(NOW(), 1) DateAfter1Month;
….—…………CurrentDate…………….|………..–.DateAfter1Month–………..
2021-11-01 14:00:11.907030000 | 2021-12-01 14:00:11.907030000

SELECT NOW() AS CurrentDate, MONTHS_SUB(NOW(), 1) DateBefore1Month;
….—…………CurrentDate…………….|………..–.DateBefore1Month–………..
2021-11-01 14:01:09.211529000 | 2021-10-01 14:01:09.211529000

If you wish to have only date, then you can use TRUNC function to truncate ‘timestamp’ from the date, as shown below.

SELECT TRUNC(NOW(),’dd’) DtNow, MONTHS_ADD(TRUNCT(NOW(),’dd’),1) Dt1MonthLater;
…………….DtNow……..|……..Dt1MonthLater……
2021-11-01 00:00:00 | 2021-12-01 00:00:00

I hope you found this post to be informative.

Please join our mailing list to receive more interesting information.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s