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.

## No comments:

## Post a Comment

To prevent "comment spam" all comments are moderated.

Learn about this website's comments policy

here.Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the

Contact Formto send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.