Date differences in Hours, Minutes, Seconds
Birgitt...@lp-gmbh.com
the easiest way to calculate the time difference between two times in
hours, minutes, seconds is to use embedded SQL.
Example:
D DiffTime DS inz
D DiffHours 2 0
D DiffMin 2 0
D DiffSec 2 0
D Time1 S T inz(T'06.20.05')
D Time2 S T inz(T'10.10.10')
*----------------------------------------------------------
* DiffTime = 035005 (3 Hours, 50 Minutes, 5 Seconds)
C/EXEC SQL Set :DiffTime = :Time2 - :Time1
C/END-EXEC
Birgitta
Bill
OS/400? Wouldn't it add unnecessary overhead of calling the sql to calc the
time difference as opposed to just using add/sub/dur?
It's like writing a book mixed English and French and expecting someone to
read it.
Thnx
Bill
<Birgitt...@lp-gmbh.com> wrote in message
news:1126551596.9...@o13g2000cwo.googlegroups.com...
Jonathan Ball
> Why does every one assume that all have and use SQL? Is it incorporated in
> OS/400?
Beginning with V5R1, it is.
> Wouldn't it add unnecessary overhead of calling the sql to calc the
> time difference as opposed to just using add/sub/dur?
Maybe some. But there's overhead as well in doing the %DIFF built-in
function (or the equivalent SUBTRACT-DURATION intrinsic function in
COBOL), *and* you're going to have to do a whole lot more operations,
involving disassembling the components of the subtrahend into hours,
minutes and seconds, then performing a separate %DIFF operation each.
If you wanted to do it using only high-level language processing, I
would recommend creating a time-difference function in a service
program, but I think there probably are more programmers who are
comfortable with embedded SQL and uncomfortable with ILE procedures
than the other way around.
> It's like writing a book mixed English and French and expecting someone to
> read it.
That seems a little strong to me. Embedded SQL is pretty widely
encountered now.
Grumman
> Bill wrote:
>>Wouldn't it add unnecessary overhead of calling the sql to calc the
>>time difference as opposed to just using add/sub/dur?
> Maybe some. But there's overhead as well in doing the %DIFF built-in
> function (or the equivalent SUBTRACT-DURATION intrinsic function in
> COBOL), *and* you're going to have to do a whole lot more operations,
> involving disassembling the components of the subtrahend into hours,
> minutes and seconds, then performing a separate %DIFF operation each.
Why would you need to do a bunch of separate %DIFFs?
http://publib.boulder.ibm.com/iseries/v5r1/ic2924/books/c0925083555.htm#HDRBBDIF
If you need the specific bits, Hours, Minutes, etc, you can either just
use %SUBDT on the result of %DIFF, or the %HOUR, %MINUTE, etc operators.
But back to the OP, the only way to know for sure is to benchmark the
different versions and see. Personally, the embedded SQL version just
looks like a lot more hassle to solve a problem that already has a
simple, builtin solution.
Jonathan Ball
> Jonathan Ball wrote:
>
>> Bill wrote:
>
> >>Wouldn't it add unnecessary overhead of calling the sql to calc the
>
>>> time difference as opposed to just using add/sub/dur?
>>
>> Maybe some. But there's overhead as well in doing the %DIFF built-in
>> function (or the equivalent SUBTRACT-DURATION intrinsic function in
>> COBOL), *and* you're going to have to do a whole lot more operations,
>> involving disassembling the components of the subtrahend into hours,
>> minutes and seconds, then performing a separate %DIFF operation each.
>
>
> Why would you need to do a bunch of separate %DIFFs?
Because if you do
diffMinutes = %diff(time2:time1:*minutes)
it is going to express the *entire* time difference
between the two times in minutes, not just the the
minutes portions of the two times. %diff isn't even
the correct one to us.
Did you see my reply to another poster's similar
question? He asked something very similar to the
question Birgitta was answering, except he had just the
hours and minutes; no seconds (he also may have been
asking specifically for an RPG/400 solution). Anyway,
it took three distinct operations to get the result in
native RPGLE code, versus one in SQL (but as I noted in
a followup, the RPGLE code ran in 1/4 the time over
50,000 iterations.)
d hourmin1 s 4 0 inz(0525)
d hourmin2 s like(hourmin1)
inz(2020)
d time1 s t
d time2 s like(time1)
d wktime s like(time1)
D DiffTimeDS DS inz
D DiffTime 6 0
D DiffHours 2 0 overlay(difftime)
D DiffMin 2 0
overlay(difftime:*next)
D DiffSec 2 0
overlay(difftime:*next)
/free
time1 = %time(hourmin1 * 100);
time2 = %time(hourmin2 * 100);
wktime = time2 - %minutes(%subdt(time1:*minutes));
wktime = wktime - %hours(%subdt(time1:*hours));
difftime = %dec(wktime);
// Diffhours = 14, Diffmin = 55
/end-free
You could combine the the two calculations into one
statement, but that's going to be unreadable code.
Note that the SQL solution to the same problem involved
one operation, although that operation ran four times
as long:
c/exec sql
c+ set :difftime = :time2 - :time1
c/end-exec
// Diffhours = 14, Diffmin = 55