Wednesday, March 15, 2023

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:

                                        - FROM_TIMESTAMP)),'999G999G999') 
                                 AS "Old diff secs",
                                        - FROM_TIMESTAMP)),'999G999G999') 
                                 AS "New diff secs"

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.


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


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:

--------------------------  --------------------------
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:

                                 - FROM_TIMESTAMP)),'999G999G999') 
                                 AS "Old diff ms",       
                                 - FROM_TIMESTAMP)),'999G999G999G999G999') 
                                 AS "New diff ms"

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

The results from this statement are:

--------------------------  --------------------------
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.

                                 - FROM_TIMESTAMP)),'999G999G999') 
                   AS "Old diff ms",
                                          - FROM_TIMESTAMP)) MICROSECONDS 
                   AS "Old calc ts",
                                     - FROM_TIMESTAMP)),'999G999G999G999G999') 
                   AS "New diff ms",
                                          - FROM_TIMESTAMP)) MICROSECONDS
               AS "New calc ts"

The results are:

--------------------------  --------------------------
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.

1 comment:

  1. IBM does warn that TIMESTAMPDIFF is better at estimating dates when they are closer to each other. In the middle of the docs page for TIMESTAMPDIFF

    is their documented 'estimation' rules:

    The following assumptions are used when converting the element values to the requested interval type:

    One year has 365 days.
    One year has 52 weeks.
    One year has 12 months.
    One quarter has 3 months.
    One month has 30 days.
    One week has 7 days.
    One day has 24 hours.
    One hour has 60 minutes.
    One minute has 60 seconds.
    One second has 1000000 microseconds.
    The use of these assumptions imply that some result values are an estimate of the interval.

    So yes, TIMESTAMPDIFF would be 'clock/human' accurate to any dates within ~30 days of each other, (or when calculating differences between anything at the scale of 'days or faster') but at the time scales of months or slower it becomes an estimation only.


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 Form to 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.