Thursday, February 11, 2021

Calculating difference between timestamps in SQL

calculate differences between timestamp variables in sql

Update: In 2023 an improved function to do the same was released. You can read about it here.


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.

6 comments:

  1. Gracias por compartir..

    ReplyDelete
  2. Another fantastic example of what's achievable

    ReplyDelete
  3. What are the pros and cons for storing timestamps in local or UTC time? When people are storing timestamps, is there a preferred number of decimals people are using?

    ReplyDelete
  4. How do I calculate the time difference between the current time and the time from a file?

    ReplyDelete
    Replies
    1. Use CURRENT_TIMESTAMP to replace the END_TIMESTAMP in the examples I have given above.

      Delete

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.