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?
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
Still have questions? We can help. Submit a case to Technical Support.