Multiple Built-in Functions to add days to a date in Impala

DATE ADD, ADDDATE, and DAYS ADD are three built-in functions in Apache Impala that do the same thing. However, the primary objective of these functions is to add days or specific time-frame to a given date, which is useful for data analysis and business logic implementation.

ADDDATE : Add number of days to a date
DATE_ADD : Add number of days or specific intervals to a date
DAYS_ADD: Add number of days to a date

Let’s look what documentation is saying:

ADDDATE(TIMESTAMP startdate, INT days), ADDDATE(TIMESTAMP startdate, BIGINT days)
Purpose: Adds a specified number of days to a TIMESTAMP value. Similar to DATE_ADD(), but starts with an actual TIMESTAMP value instead of a string that is converted to a TIMESTAMP.
Return type: TIMESTAMP

DATE_ADD(TIMESTAMP startdate, INT days), 
DATE_ADD(TIMESTAMP startdate, interval_expression)
Purpose: Adds a specified number of days to a TIMESTAMP value. With an INTERVAL expression as the second argument, you can calculate a delta value using other units such as weeks, years, hours, seconds, and so on; see TIMESTAMP Data Type for details.
Return type: TIMESTAMP

DAYS_ADD(TIMESTAMP startdate, INT days), DAYS_ADD(TIMESTAMP startdate, BIGINT days)
Purpose: Adds a specified number of days to a TIMESTAMP value. Similar to DATE_ADD(), but starts with an actual TIMESTAMP value instead of a string that is converted to a TIMESTAMP.
Return type: TIMESTAMP

Examples:
SELECT NOW() AS CurrentDate, ADDDATE(NOW(), 30) DateAfter30Days;
….—…………CurrentDate…………….|………..–.DateAfter30Days–………..
2021-10-30 09:15:41.895351000 | 2021-11-29 09:15:41.895351000

By entering a negative number for the number of days, we can get a date that is 30 days older. +30 indicates a date in the future, whereas -30 indicates a date in the past.

SELECT NOW() AS CurrentDate, DATE_ADD(NOW(), 30) AS DateAfter30Days;
….—…………CurrentDate…………….|………..–.DateAfter30Days–………..
2021-10-30 09:15:41.895351000 | 2021-11-29 09:15:41.895351000

We can supply not only a number of days, but also a number of weeks, years, hours, seconds, and so on;. Take a look at the example below.

SELECT NOW() AS CurrentDate, DATE_ADD(NOW(), interval 3 weeks) AS After3Weeks;
….—…………CurrentDate…………….|………..–.DateAfter3Weeks–………..
2021-10-30 09:15:41.895351000 | 2021-11-20 09:15:41.895351000

SELECT NOW(), DATE_ADD(NOW(), interval 6 hours);
SELECT DATE_ADD(CAST(‘2016-01-31’ AS TIMESTAMP), interval 3 months);

Now, let’s take a look at “DAYS_ADD” function.

SELECT NOW() AS CurrentDate, DAYS_ADD(NOW(), 30) AS DateAfter30Days;
….—…………CurrentDate…………….|………..–.DateAfter30Days–………..
2021-10-30 09:15:41.895351000 | 2021-11-29 09:15:41.895351000

There isn’t much of a difference between these three functions when it comes to adding a number of days to a specific date. In order to add or substract days, hours, months, weeks, years, seconds, milliseconds and microseconds, there are different built-in functions available which will be discussed in the upcoming articles.

Hope you find this article helpful.

Please subscribe for more interesting updates.