Date arithmetic functions added to SQL

In the latest round of Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, a number of date arithmetic functions were added to SQL.

I have to admit I was surprised by this as I did not consider the way I had been doing date arithmetic with SQL as lacking.

These new SQL functions are:

• ADD_YEARS
• ADD_MONTHS
• ADD_DAYS
• ADD_HOURS
• ADD_MINUTES
• ADD_SECONDS

They all have the same syntax:

01 VALUES ADD_XXXXX(CURRENT_TIMESTAMP, 10) ; 02 VALUES ADD_XXXXX(CURRENT_DATE, -1) ; 03 VALUES ADD_XXXXX(CURRENT_TIME, 30) ;

I have used XXXX a placeholder for the various date and time units.

Line 1: All of the new functions can be used with a timestamp. Here I am adding 10 whatever the function is to the timestamp.

Line 2: As this is a date only the date functions can be used, ADD_YEARS, ADD_MONTHS, ADD_DAYS. I am adding a negative number to the date, which means it will subtract 1 from whatever the function's units are from the date.

Line 3: As this is a time only the time functions can be used, ADD_HOURS, ADD_MINUTES, ADD_SECONDS. I am adding 30 whatever to the function's units are to the time.

Let me jump into some better examples, starting with the ADD_YEAR function:

01 SELECT TS_COLUMN, 02 ADD_YEARS(TS_COLUMN, 1) AS "+1 year", 03 ADD_YEARS(TS_COLUMN, -1) AS "-1 year", 04 ADD_YEARS(DATE(TS_COLUMN), 2) AS "+2 years", 05 YEAR(ADD_YEARS(TS_COLUMN, -2)) AS "-2 yr" 06 FROM TESTTABLE

Line 1: The timestamp column in the DDL table TESTTABLE.

Line 2: Adding 1 year to TS_COLUMN. I have added to all of the columns created by using these new functions a heading to describe what happened.

Line 3: Subtract 1 year from TS_COLUMN.

Line 4: Convert TS_COLUMN to a date with the DATE function, and then add 2 years to that date.

Line 5: I wanted to subtract 2 years from the year I extracted from TS_COLUMN. Had to first subtract 2 years from TS_COLUMN and then use the YEAR to extract the year from it.

The results are:

TS_COLUMN +1 year -1 year -------------------------- -------------------------- -------------------------- 2023-07-01 08:10:12.612000 2024-07-01 08:10:12.612000 2022-07-01 08:10:12.612000 2023-08-11 12:08:29.992000 2024-08-11 12:08:29.992000 2022-08-11 12:08:29.992000 +2 years -2 yr ---------- ----- 2025-07-01 2021 2025-08-11 2021

Before these TRs I would have used the following statement to do the same:

01 SELECT TS_COLUMN, 02 TS_COLUMN + 1 YEAR AS "+1 year", 03 TS_COLUMN - 1 YEAR AS "-1 year", 04 DATE(TS_COLUMN) + 2 YEARS AS "+2 years", 05 YEAR(TS_COLUMN - 2 YEARS) AS "-2 yr" 06 FROM TESTTABLE

Up next is the ADD_MONTHS function. I have done something different here. To show how the results are the same I have used the UNION clause to combine a Select statement using the new ADD_MONTHS with the way I would have done it before.

01 SELECT '1' AS "T", 02 TS_COLUMN, 03 TS_COLUMN + 1 MONTH AS "+1 month", 04 DATE(TS_COLUMN) - 2 MONTHS AS "-2 mth", 05 MONTH(TS_COLUMN + 6 MONTHS) AS "+6 mth" 06 FROM TESTTABLE 07 UNION 08 SELECT '2',TS_COLUMN, 09 ADD_MONTHS(TS_COLUMN, 1), 10 ADD_MONTHS(DATE(TS_COLUMN), -2), 11 MONTH(ADD_MONTHS(TS_COLUMN, 6)) 12 FROM TESTTABLE 13 ORDER BY 2,1

Lines 1 and 8: I have created this column so I can identify which result comes from which statement. "1" is not using the new functions, and "2" denotes that the functions were used.

Lines 3 – 5: Here I am performing the date math without the functions. Notice on line 5 I have to add the months to the timestamp, and then extract the month from the result.

Lines 9 – 11: Here I am doing the same as I did in lines 3 – 5, but I am using ADD_MONTHS functions instead.

It is worth noticing that I have given all the column headings in the first Select statement, and they are not needed in the second.

The results show that there is no difference between results from the two methods:

T TS_COLUMN +1 month -2 mth +6 mth - -------------------------- -------------------------- ---------- ------ 1 2023-07-01 08:10:12.612000 2023-08-01 08:10:12.612000 2023-05-01 1 2 2023-07-01 08:10:12.612000 2023-08-01 08:10:12.612000 2023-05-01 1 1 2023-08-11 12:08:29.992000 2023-09-11 12:08:29.992000 2023-06-11 2 2 2023-08-11 12:08:29.992000 2023-09-11 12:08:29.992000 2023-06-11 2

The last function related to dates is ADD_DAYS:

01 SELECT TS_COLUMN, 02 ADD_DAYS(TS_COLUMN, 10) AS "+10 days", 03 ADD_DAYS(DATE(TS_COLUMN), -1) AS "-1 days", 04 DAY(ADD_DAYS(TS_COLUMN, 6)) AS "+6 d" 05 FROM TESTTABLE

Line 2: I am adding 10 days to the time stamp.

Line 3: I am converting the timestamp to the date, with the DATE function, and then I am subtracting a day from the created date.

Line 4: To extract the changed day of the month I have to add 6 days to the timestamp, and then extract the day of the month using the DAY function.

The results are:

TS_COLUMN +10 days -1 days +6 d -------------------------- -------------------------- ---------- ----- 2023-07-01 08:10:12.612000 2023-07-11 08:10:12.612000 2023-06-30 7 2023-08-11 12:08:29.992000 2023-08-21 12:08:29.992000 2023-08-10 17

Having covered the date specific function, no I am moving onto the time ones: ADD_HOURS, ADD_MINUTES, and ADD_SECONDS. I am going to use these all in one statement:

01 SELECT TIME(TS_COLUMN) AS "TS_COLUMN", 02 TIME(ADD_HOURS(TS_COLUMN, 12)) AS "+12 hrs", 03 TIME(ADD_MINUTES(TS_COLUMN, -10)) AS "-10 mins", 04 TIME(ADD_SECONDS(TS_COLUMN, 30)) AS "+30 secs" 05 FROM TESTTABLE

Do I really need to describe what these functions do? The syntax is the same as the other functions.

The results are:

TS_COLUMN +12 hrs -10 mins +30 secs --------- -------- -------- -------- 08.10.12 20.10.12 08.00.12 08.10.42 12.08.29 00.08.29 11.58.29 12.08.59

If I was to do this without using the new functions I would:

01 SELECT TIME(TS_COLUMN) AS "TS_COLUMN", 02 TIME(TS_COLUMN) + 12 HOURS AS "+12 hrs", 03 TIME(TS_COLUMN) - 10 MINUTES AS "-10 mins", 04 TIME(TS_COLUMN) + 30 SECONDS AS "+30 secs" 05 FROM TESTTABLE

Another way I regularly use date math is in the WHERE clause:

01 SELECT * FROM TESTTABLE 02 WHERE TS_COLUMN > ADD_DAYS(CURRENT_TIMESTAMP, 30) ; 03 SELECT * FROM TESTTABLE 04 WHERE TS_COLUMN > CURRENT_TIMESTAMP + 30 DAYS ;

Both statement return the same results, only selecting the rows where the timestamp value in TS_COLUMN is 30 days greater than now.

Having played with functions I will be sticking with the traditional way of performing date math in my SQL statements. I just think it is clearer what is being done, than with these functions. This is just my personal preference. I see no reason why people should not use the new functions if they would like to.

 

You can learn more about this from the IBM website:

• ADD_YEARS function
• ADD_MONTHS function
• ADD_DAYS function
• ADD_HOURS function
• ADD_MINUTES function
• ADD_SECONDS function

 

This article was written for IBM i 7.5 TR2 and 7.4 TR8.