Getting the hours, minutes, and seconds from a difference in timestamps with SQL

I have timestamp columns in a Db2 Table that I need to give the difference in hours, minutes, and seconds. Preferably I would put these into separate columns that I can then use in various ways in subsequent jobs and programs.

All you really needs to know about the Table I will be using is that it is called TESTTABLE, and the timestamp columns I need to calculate the difference between are:

1. FROM_TIMESTAMP
2. THRU_TIMESTAMP

I can easily determine the difference between the two with the following SQL statement:

01 SELECT THRU_TIMESTAMP - FROM_TIMESTAMP 02 FROM TESTTABLE

Line 1: I just subtract FROM_TIMESTAMP from THRU_TIMESTAMP.

The results returned are:

00001 ------------- 130.000000 14000.000000

What do those two numbers represent?

The first result shows the difference between the timestamps as being 1 minute and 30 seconds.

The second results shows the difference of 1 hours and 40 minutes.

The decimal part of both numbers is the difference between the microseconds, which I don't care about for the results I desire.

When I format the numbers using the TO_CHAR scalar function they are easier to understand:

01 SELECT THRU_TIMESTAMP - FROM_TIMESTAMP, 02 REPLACE(TO_CHAR(THRU_TIMESTAMP - FROM_TIMESTAMP,'000G00G00.000000'),',','-') 03 FROM TESTTABLE

Line 2: Let me break apart this statement to better describe it. I will be using two dots ( .. ) to denote the part of this statement I have previously described.

I am using the TO_CHAR scalar function to insert a separator character between the parts of the results: hours, minutes, and seconds. I am limited as to what characters I can use as the separator, therefore, I have used the default, which is indicated by 'G'. The default in the partition I am using is a comma ( , ). Notice that I have used zeroes ( 0 ) rather than 9s. The zeroes will keep the leading zeroes when the number is converted to a character string.

TO_CHAR( .. ,'000G00G00.000000')

Then I use the REPLACE scalar function to replace the commas with hyphens ( - ).

REPLACE( .. ,',','-')

The character string in the second column is now easier to understand as the hyphens separate the hours, minutes, and seconds.

00001 00002 ------------- ---------------- 130.000000 000-01-30.000000 14000.000000 001-40-00.000000

I could now substring the various parts of the character string into hours, minutes, and seconds columns.

I was thinking that there must be an easier way to calculate and format columns for hours, minutes, and seconds. After a bit of "play" I found another way, which I think is more intuitive, using the EXTRACT scalar function:

01 SELECT THRU_TIMESTAMP - FROM_TIMESTAMP AS "Difference", 02 EXTRACT(HOURS FROM (THRU_TIMESTAMP - FROM_TIMESTAMP)) AS "HH", 03 EXTRACT(MINUTES FROM (THRU_TIMESTAMP - FROM_TIMESTAMP)) AS "MM", 04 EXTRACT(SECONDS FROM (THRU_TIMESTAMP - FROM_TIMESTAMP)) AS "SS" 05 FROM TESTTABLE

I have added a blank line between each line of the above statement to make it easier to understand.

Line 2: I use the EXTRACT with HOURS to retrieve the number of hours from the timestamp calculation.

Line 3: This time I use the EXTRACT with MINUTES to get the number of minutes.

Line 4: EXTRACT with SECONDS to get the number of seconds.

The results are:

Difference HH MM SS ------------- --- --- --------- 130.000000 0 1 30.000000 14000.000000 1 40 0.000000

I want the leading zeroes for each of the new columns, and I want to remove the microseconds.

I modify the above statement by inserting the TO_CHAR scalar function to keep the leading zeroes, and the INT scalar function to convert the seconds, including the microseconds, into an integer.

01 SELECT THRU_TIMESTAMP - FROM_TIMESTAMP AS "Difference", 02 TO_CHAR(EXTRACT(HOURS FROM (THRU_TIMESTAMP - FROM_TIMESTAMP)),'00') AS "HH", 03 TO_CHAR(EXTRACT(MINUTES FROM (THRU_TIMESTAMP - FROM_TIMESTAMP)),'00') AS "MM", 04 TO_CHAR(INT(EXTRACT(SECONDS FROM (THRU_TIMESTAMP - FROM_TIMESTAMP))),'00') AS "SS" 05 FROM TESTTABLE

Line 2: I have added the TO_CHAR with the formatting code of '00' (two zeroes), which will retain the leading zero.

Line 4: I have used INT scalar function to convert the extract seconds and microseconds into an integer, which is just the seconds. The TO_CHAR does what is has in the other lines, convert the number to character with a leading zero.

The results are exactly what I want:

Difference HH MM SS ------------- --- --- --- 130.000000 00 01 30 14000.000000 01 40 00

I think you will agree that by using the EXTRACT I have simplified the statement, making it easier for others to understand.

 

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