Thursday, February 11, 2021

Calculating difference between timestamps in SQL

calculate differences between timestamp variables in sql

Having discussed how to make timestamp values from numbers, the next step in what I need to do was to determine the difference between the start and the end times. In this case there is a SQL function I can use: TIMESTAMPDIFF

The basic syntax of the statement is:

SELECT TIMESTAMPDIFF(< number >,CHAR(< end timestamp > - < start timestamp >))
  FROM < file >

The numbers you can use are for the following time intervals:

  • 1 = Microseconds
  • 2 = Seconds
  • 4 = Minutes
  • 8 = Hours
  • 16 = Days
  • 32 = Weeks
  • 64 = Months
  • 128 = Quarters
  • 256 = Years

Let me jump straight to some examples. In this first I am only going to calculate the differences as time values, but not microseconds. Microseconds has such a small range they could not be calculated for the timestamps used in these statements.

01  SELECT START_TIMESTAMP,END_TIMESTAMP,
02    TO_CHAR(
        TIMESTAMPDIFF(2,CHAR(END_TIMESTAMP - START_TIMESTAMP)),
        '999G999G999G999') AS "Seconds",
03    TO_CHAR(
        TIMESTAMPDIFF(4,CHAR(END_TIMESTAMP - START_TIMESTAMP)),
        '999G999G999G999') AS "Minutes",
04    TO_CHAR(
        TIMESTAMPDIFF(8,CHAR(END_TIMESTAMP - START_TIMESTAMP)),
        '999G999G999G999') AS "Hours"
05  FROM TESTFILE ;

With all of calculated columns I have used the TO_CHAR function to format the result with commas. It makes it so much easier to visualize the results.

The results look like:

START_TIMESTAMP            END_TIMESTAMP
-------------------------- --------------------------
1992-06-30 11:00:00.000000 2021-01-01 00:00:00.000000
2020-12-31 04:53:22.000000 2021-01-25 14:01:00.000000


SECONDS      MINUTES     HOURS
-----------  ----------  -------
898,606,800  14,976,780  249,613
  2,192,858      36,547      609

And now for the date calculations:

01  SELECT START_TIMESTAMP,END_TIMESTAMP,
02   TO_CHAR(
       TIMESTAMPDIFF(16,CHAR(END_TIMESTAMP - START_TIMESTAMP)),
       '999G999G999G999'),
03    TO_CHAR(
        TIMESTAMPDIFF(32,CHAR(END_TIMESTAMP - START_TIMESTAMP)),
        '999G999G999G999'),
04    TO_CHAR(
        TIMESTAMPDIFF(64,CHAR(END_TIMESTAMP - START_TIMESTAMP)),
        '999G999G999G999'),
05    TO_CHAR(
        TIMESTAMPDIFF(128,CHAR(END_TIMESTAMP - START_TIMESTAMP)),
        '999G999G999G999'),
06    TO_CHAR(
        TIMESTAMPDIFF(256,CHAR(END_TIMESTAMP - START_TIMESTAMP)),
        '999G999G999G999')
07  FROM TESTFILE ;

And the results:

START_TIMESTAMP            END_TIMESTAMP
-------------------------- --------------------------
1992-06-30 11:00:00.000000 2021-01-01 00:00:00.000000
2020-12-31 04:53:22.000000 2021-01-25 14:01:00.000000


DAYS     WEEKS   MONTHS   QUARTERS   YEARS
------   -----   ------   --------   -----
10,400   1,481      342        114      28
    25       3        0          0       0

When using the TIMESTAMPDIFF there are some assumptions for years and months that can produce inaccurate results:

  1. Year = 365 days
  2. Year = 52 weeks
  3. Month = 30 days

The lack of leap years, 53 week years, and months of 31 and 28 days are something to be aware of.

I only use this function to calculate the difference of time, normally seconds, as most timestamps I handle on a daily basis cover a period of time that is rarely more than 24 hours.

 

You can learn more about the TIMESTAMPDIFF function from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

3 comments:

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.