Calculate Time Durration from Date and Numeric Time Fields

Problem Statement:

Starting with World software record containing a date value along with numeric begin and end time fields in the format of HHMMSS, what is the calculation for elapsed time expressed as HH:MM:SS?

Solution:

This solution requires using actual timestamps with the TIMESTAMPDIFF, SUBSTRING and MOD functions. Since the example uses actual timestamps it will account for varying month lengths as well as year changes.

You can copy the expression to a standard text editor, and  do a mass change of the STARTTIME and ENDTIME strings to the names of your respective beginning and ending timestamp fields.

EXAMPLE Select Statement:

SELECT
STARTTIME,
ENDTIME,
SUBSTRING(STRIP(DIGITS((TIMESTAMPDIFF(SQL_TSI_HOUR, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME))*10000) 
+ (MOD(TIMESTAMPDIFF(SQL_TSI_MINUTE, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)*100) 
+ MOD(TIMESTAMPDIFF(SQL_TSI_SECOND, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)), LEADING,'0'), 1, 
LENGTH(STRIP(DIGITS((TIMESTAMPDIFF(SQL_TSI_HOUR, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)) * 10000) 
+ (MOD(TIMESTAMPDIFF(SQL_TSI_MINUTE, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)*100) 
+ MOD(TIMESTAMPDIFF(SQL_TSI_SECOND, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)), LEADING,'0'))-4) || ':' 
|| SUBSTRING(STRIP(DIGITS((TIMESTAMPDIFF(SQL_TSI_HOUR, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME))*10000) 
+ (MOD(TIMESTAMPDIFF(SQL_TSI_MINUTE, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)*100) 
+ MOD(TIMESTAMPDIFF(SQL_TSI_SECOND, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)), LEADING,'0'), 
LENGTH(STRIP(DIGITS((TIMESTAMPDIFF(SQL_TSI_HOUR, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME))*10000)
+ (MOD(TIMESTAMPDIFF(SQL_TSI_MINUTE, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)*100) 
+ MOD(TIMESTAMPDIFF(SQL_TSI_SECOND, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)), LEADING,'0'))- 3, 2) || ':' 
|| SUBSTRING(STRIP(DIGITS((TIMESTAMPDIFF(SQL_TSI_HOUR, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME))*10000) 
+ (MOD(TIMESTAMPDIFF(SQL_TSI_MINUTE, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)*100) 
+ MOD(TIMESTAMPDIFF(SQL_TSI_SECOND, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)), LEADING,'0'), 
LENGTH(STRIP(DIGITS((TIMESTAMPDIFF(SQL_TSI_HOUR, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME))*10000) 
+ (MOD(TIMESTAMPDIFF(SQL_TSI_MINUTE, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)*100) 
+ MOD(TIMESTAMPDIFF(SQL_TSI_SECOND, TIMESTAMP(ENDTIME), TIMESTAMP(STARTTIME)), 60)), LEADING,'0'))- 1, 2)
AS HHMMSS

Ref#: 1480543