Re: Difference between 2 time values expressed in hh:mm:ss format

There is date/time/timestamp arithmetic in SQL - you can subtract timestamps, for example. The result is a "duration", which has a weird = to me = layout for timestamps, it is a 20,6 packed number - 1st 4 digits are number of years between the timestamps, next 2 the # of months, etc., down to microseconds. I've never seen this as a "simple-to-use" thing in SQL, but one could make a DS with a zoned subfield and appropriate "component" subfields for year, month, day, etc. - unless one can extract all that using the various date built-ins.

I mention this option, admitting that I've never quite conquered it.

For example, the result of TIME('11:02:26') - '00:32:56' is 102930 (a duration of 10 hours, 29 minutes, and 30 seconds).
For example, the result of DATE('3/15/2000') - '12/31/1999' is 215 (or, a duration of 0 years, 2 months, and 15 days). (Date duration is 8,0 packed)

A timestamp duration would be formatted as YYYYMMDDHHMMSS.mmmmmm

HTH
Vern

Jeff Young wrote:

Scott,
Given that concern, what would you recommend as the best method to get a duration value of the difference between two timestamps in HH:MM:SS format?

Thanks,

Jeff Young Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 IBM Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3





________________________________
From: Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Sent: Mon, May 24, 2010 1:40:09 PM
Subject: Re: Difference between 2 time values expressed in hh:mm:ss format

Hi Brian,

When you give this answer again (and you will -- I've given it at least twice a month for the past 4 years) please add a disclaimer.

The disclaimer is that the TIME data type was not intended to store a DURATION. It was meant to identify a particular moment of the day, not to express an amount of time that has passed.

Consequently, it does not work well for this sort of thing. For example, if there's 15 hours and 20 minutes have elapsed between a start/end timestamp, and you format the time in USA format, it'll say "3:20 PM" It identifies a time of the day, not a number of hours that elapsed.

Likewise, if you try to exceed 24 hours, you'll get an error. For example, you can't make it say 26 hrs, 5 minutes. Why not? Because it identifies a time of day, and there's no 26 O'Clock.

So while the technique you propose does have value in certain circumstances, you should understand that you're doing something that wasn't really intended -- and therefore won't always do what you expect.


On 5/24/2010 9:04 AM, BMay@xxxxxxxxx wrote:

Use the %Diff Bif to get the difference in seconds. Then add the seconds
to a time field set for midnight (00:00:00).