Thursday, March 16, 2023

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.

No comments:

Post a Comment

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.