Calculating difference between timestamps 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.