Improved SQL built in function for calculating difference between timestamps

I had written about the Db2 built in function TIMESTAMPDIFF, which is used to calculate the difference between two timestamps and return the difference in various different units of time. In the latest Technology Refreshes, IBM i 7.5 TR1 and IBM i 7.4 TR7, a new and improved built in function, TIMESTAMPDIFF_BIG, was introduced.

TIMESTAMPDIFF had issues in being able to correctly return the difference in microseconds. IBM's documentation says that the new TIMESTAMPDIFF_BIG will do that correctly. The syntax of the two built in functions, BiF, is the same:

TIMESTAMPDIFF(<unit-of-time> , CHAR(<timestamp-1> - <timestamp-2>)) TIMESTAMPDIFF_BIG(<unit-of-time> , CHAR(<timestamp-1> - <timestamp-2>))

I am not going to go into detail about the unit of time parameter as I covered that in the post about TIMESTAMPDIFF. I will just be using three of these codes in these examples:

• 1 = Microseconds
• 2 = Seconds
• 16 = Days

I have a SQL Table that contains two timestamps that I will be using. These are:

• FROM_TIMESTAMP:  The starting timestamp
• THRU_TIMESTAMP:  The ending timestamp

In this first example I am going to show the results from both of the BiFs calculation the difference between the two timestamp columns in seconds:

01 SELECT FROM_TIMESTAMP,THRU_TIMESTAMP, 02 TO_CHAR(TIMESTAMPDIFF(2,CHAR(THRU_TIMESTAMP - FROM_TIMESTAMP)),'999G999G999') AS "Old diff secs", 03 TO_CHAR(TIMESTAMPDIFF_BIG(2,CHAR(THRU_TIMESTAMP - FROM_TIMESTAMP)),'999G999G999') AS "New diff secs" 04 FROM TESTTABLE ;

Line 1: I want the two timestamp columns returned in the results.

Line 2: This is a long line, but once you understand all the parts you see it is not that hard to understand.

CHAR(THRU_TIMESTAMP - FROM_TIMESTAMP)

I need to convert the result of the subtraction of the two timestamps into a character value.

TIMESTAMPDIFF(2 , .. )

I am using the two dots ( .. ) to signify that this is where the previous statement goes.

TIMESTAMPDIFF has two parameters:

• 2 = I want the results in second
• The result of the difference of the timestamps as character

TIMESTAMPDIFF return an integer.

TO_CHAR( .. ) , '999G999G999')

TO_CHAR converts the number passed to it to a character value and, in this case, inserts thousand separators. IMHO this makes it easier to understand the size of the number.

Line 3: I do the same as above with TIMESTAMPDIFF_BIG.

The results are:

FROM_TIMESTAMP THRU_TIMESTAMP -------------------------- -------------------------- 1995-02-21 11:12:00.000000 2023-01-01 19:23:45.360284 Old diff secs New diff secs ------------- ------------- 878,112,705 878,112,705

The timestamp differences are the same no matter which BiF I used.

Next example let me do the same, but I want my results in microseconds:

01 SELECT FROM_TIMESTAMP,THRU_TIMESTAMP, 02 TO_CHAR(TIMESTAMPDIFF(1,CHAR(THRU_TIMESTAMP - FROM_TIMESTAMP)),'999G999G999') AS "Old diff ms", 03 TO_CHAR(TIMESTAMPDIFF_BIG(1,CHAR(THRU_TIMESTAMP - FROM_TIMESTAMP)),'999G999G999G999G999') AS "New diff ms" 04 FROM TESTTABLE ;

This statement is almost same as the other. The only difference is the unit of time code.

The results from this statement are:

FROM_TIMESTAMP THRU_TIMESTAMP -------------------------- -------------------------- 1995-02-21 11:12:00.000000 2023-01-01 19:23:45.360284 Old diff ms New diff ms ------------ ------------------- 705,360,284 878,112,705,360,284

The difference between the results returned from the two BiF is considerable. I would say that the microseconds value returned by TIMESTAMPDIFF is way off being the true result. I can create a statement that I will add the number of microseconds returned from each BiF to FROM_TIMESTAMP to see how close the result of this calculation is to the value in THRU_TIMESTAMP.

01 SELECT FROM_TIMESTAMP,THRU_TIMESTAMP, 02 TO_CHAR(TIMESTAMPDIFF(1,CHAR(THRU_TIMESTAMP - FROM_TIMESTAMP)),'999G999G999') AS "Old diff ms", 03 FROM_TIMESTAMP + TIMESTAMPDIFF(1,CHAR(THRU_TIMESTAMP - FROM_TIMESTAMP)) MICROSECONDS AS "Old calc ts", 04 TO_CHAR(TIMESTAMPDIFF_BIG(1,CHAR(THRU_TIMESTAMP - FROM_TIMESTAMP)),'999G999G999G999G999') AS "New diff ms", 05 FROM_TIMESTAMP + TIMESTAMPDIFF_BIG(1,CHAR(THRU_TIMESTAMP - FROM_TIMESTAMP)) MICROSECONDS AS "New calc ts" 06 FROM TESTTABLE ;

The results are:

FROM_TIMESTAMP THRU_TIMESTAMP -------------------------- -------------------------- 1995-02-21 11:12:00.000000 2023-01-01 19:23:45.360284 Old diff ms Old calc ts ------------ -------------------------- 705,360,284 1995-02-21 11:23:45.360284 New diff ms New calc ts ------------------- -------------------------- 878,112,705,360,284 2022-12-19 19:23:45.360284

The timestamp calculated using the microseconds from the TIMESTAMPDIFF BiF has not even moved to the next day.

Even the timestamp calculated with the microseconds from the TIMESTAMPDIFF_BIG is closer, but is not equal to the THRU_TIMESTAMP.

I know that the TIMESTAMPDIFF does not take into consideration the extra day in leap years when performing its calculations. Does TIMESTAMPDIFF_BIG do the same?

If I calculate the difference in days for a non-leap year, 2023-01-01 to 2024-01-01, and do the same for a leap year, 2024-01-01 to 2025-01-01. I would expect the first difference to return 365 and the second difference return 366. This is the statement I used to test this:

01 VALUES (TIMESTAMPDIFF(16,CHAR(TIMESTAMP('2025-01-01 00:00:00.000000') - TIMESTAMP('2024-01-01 00:00:00.000000'))), 02 TIMESTAMPDIFF(16,CHAR(TIMESTAMP('2024-01-01 00:00:00.000000') - TIMESTAMP('2023-01-01 00:00:00.000000')))), 03 (TIMESTAMPDIFF_BIG(16,CHAR(TIMESTAMP('2025-01-01 00:00:00.000000') - TIMESTAMP('2024-01-01 00:00:00.000000'))), 04 TIMESTAMPDIFF_BIG(16,CHAR(TIMESTAMP('2024-01-01 00:00:00.000000') - TIMESTAMP('2023-01-01 00:00:00.000000')))) ;

Line 1: This calculates the difference of days for 2023 using TIMESTAMPDIFF.

Line 2: This does the same for the leap year 2024 using TIMESTAMPDIFF.

Line 3: Difference of days for 2023 with TIMESTAMPDIFF_BIG.

Line 4: Leap year 2024 difference with TIMESTAMPDIFF_BIG.

I have enclose lines 1 and 2 in a set of parentheses ( ( ) ), and done the same for lines 3 and 4. As I have used the SQL VALUES statement this will produce two rows of result, the first for the 1st and 2nd lines, and the second row for the 3rd and 4th.

The results are:

00001 00002 ------ ------ 365 365 365 365

Disappointingly the number of non-leap year days, column 00001, is the same as the number of days for the leap year days, column 0002.

I assuming that the number days short the recalculated timestamp that used TIMESTAMPDIFF_BIG was due to the extra days from the intervening leap years.

I would use the TIMESTAMPDIFF_BIG in preference to the other, but would be wary of the results for periods of time that exceed a day or more.

 

You can learn more about the TIMESTAMPDIFF_BIG SQL BiF from the IBM website here.

 

This article was written for IBM i 7.5 TR1 and 7.4 TR7.