It's not SQL, but TAATool has a command CLCTIMDIF which will calculate
time difference, which I have found very useful.
Sheri
-----Original Message-----
From: CRPence [mailto:CRPbottle@xxxxxxxxx]
Sent: Saturday, April 03, 2010 7:41 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Use sql to calculate a time difference between 2 records
Dan Kimmel wrote:
Would something like this work?
select
timestampfield
, ( (select min(c.timestampfield)
from timefile c
where c.timestampfield > a.timestampfield
) - a.timestampfield
) as timediff
from timefile a
Alan Shore wrote:
I have a non-keyed file that is in chronological order. The
record contains a timestamp field. How can I use SQL to
determine the time difference (in seconds and parts of seconds)
between one record and the next
As always any responses are MUCH appreciated
Assuming that the difference between any two rows can be assured
to never reach sixty seconds, then the quoted expression [a column
subtracted from a subselect result] yielding a "TIMESTAMP duration"
would be sufficient to express the difference "in seconds and parts
of seconds". That is because the DECIMAL(20,6) result as TIMESTAMP
duration for the subtraction of TIMESTAMP values is a decimal value
that expresses "the number of years, months, days, hours, minutes,
seconds, and microseconds between the two timestamps" rather than a
decimal value that expresses the desired "seconds and parts of seconds".
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzt
imestamparith.htm
However if the difference may reach one minute, but does not
exceed the maximum allowed difference for the microseconds result of
the TIMESTAMPDIFF scalar [something like 34 minute maximum?], then
the quoted expression as the second column of the result set could
be rewritten as:
decimal(
timestampdiff( 1, cast(
the_given_expression as char(22))
) / 1000000.0
, 10, 6) as timediff
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzs
catimedifstmp.htm
Such assurances of the data [for a maximum difference] may not be
possible. So if the difference between two rows may exceed the
noted microseconds difference limit [for the TIMESTAMPDIFF scalar],
then the various portions of the TIMESTAMP duration would need to be
calculated in seconds [estimated seconds; e.g. if reaching months in
duration difference] & microseconds, and then added to one another.
That is, implement similar effects of the TIMESTAMPDIFF scalar,
but with the result expressed only as the total of the seconds and
microseconds. A possibly illuminating example:
<code>
create table chrono
( r decimal(1, 0), ts timestamp )
;
insert into chrono values
( 1, '2010-01-01-00.00.00.000000')
,( 2, '2010-01-01-00.00.00.000005')
,( 3, '2010-01-01-00.00.05.000005')
,( 4, '2010-01-01-00.05.05.000005')
,( 5, '2010-01-01-05.05.05.000005')
,( 6, '2010-01-06-05.05.05.000005')
,( 7, '2010-06-06-05.05.05.000005')
,( 8, '2015-06-06-05.05.05.000005')
;
create view diffxmp (r, ts0, ts1, tsdiff)
as ( select a.r, a.ts
, (select min(s1.ts) from chrono s1
where s1.ts > a.ts)
, (select min(s1.ts) from chrono s1
where s1.ts > a.ts)
- a.ts
from chrono a
)
;
create view diffinsecs
(r, ts0, ts1, tsdiffdur, tsdiffsec) as
(select r, ts0, ts1
, digits(tsdiff)
, decimal(
year (tsdiff) * 365 * 24 * 60 * 60
+ month (tsdiff) * 30 * 24 * 60 * 60
+ day (tsdiff) * 24 * 60 * 60
+ hour (tsdiff) * 60 * 60
+ minute (tsdiff) * 60
+ second (tsdiff)
+ decimal(
microsecond(tsdiff) / 1000000. , 6, 6)
, 20, 6)
from diffxmp
)
;
select r
, digits(tsdiff) as tsdiff
, year (tsdiff) * 365 * 24 * 60 * 60
+ month (tsdiff) * 30 * 24 * 60 * 60
+ day (tsdiff) * 24 * 60 * 60
+ hour (tsdiff) * 60 * 60
+ minute (tsdiff) * 60
+ second (tsdiff)
+ decimal(
microsecond(tsdiff) / 1000000. , 6, 6)
as "DiffInSeconds"
from diffxmp
UNION ALL
select 0.
, 'YYYYMMDDHHMMSSMMMMMM'
, cast(null as decimal)
from qsqptabl
order by 1
;
....+....1....+....2....+....3....+....4....+....
R TSDIFF "DiffInSeconds"
0 YYYYMMDDHHMMSSMMMMMM -
1 00000000000000000005 .000005
2 00000000000005000000 5.000000
3 00000000000500000000 300.000000
4 00000000050000000000 18,000.000000
5 00000005000000000000 432,000.000000
6 00000500000000000000 12,960,000.000000
7 00050000000000000000 157,680,000.000000
8 - -
******** End of data ********
-- or almost as above from the VIEW over the VIEW:
select r, tsdiffdur, tsdiffsec
from diffinsecs
UNION ALL
select 0.
, 'YYYYMMDDHHMMSSMMMMMM'
, cast(null as decimal)
from qsqptabl
order by 1
;
</code>
Regards, Chuck
midrange.com
