Re: How to add two Datetime RANGES ? (145 Views)
Frequent Advisor
Posts: 54
Registered: ‎08-03-2006
Message 1 of 9 (145 Views)

How to add two Datetime RANGES ?

My values in a table are -->
2006-07-01 07:10:22 2006-07-01 09:11:26
2006-07-01 08:10:22 2006-07-01 12:11:26

I want to find the interval of each row and add the intervals. How can it be done ?
We have to be careful about the overlapping date time.
The result should be in Hrs:Min:Sec format.
Please use plain text.
Honored Contributor
Posts: 725
Registered: ‎05-18-2000
Message 2 of 9 (145 Views)

Re: How to add two Datetime RANGES ?

create table d (f1 integer, dt1 datetime year to second, dt2 datetime year to second);

insert into d values( 1 , "2006-07-01 07:10:22" , "2006-07-01 09:11:26" );
insert into d values( 2 , "2006-07-01 08:10:22" , "2006-07-01 12:11:26" );

select ((x.dt2 - x.dt1) + (y.dt2 - y.dt1) ) from d x, d y where x.f1=1 and y.f1=2 ;

Gives the following output:

(expression)

0 06:02:08

Please use plain text.
Exalted Contributor
Posts: 33,806
Registered: ‎08-15-2002
Message 3 of 9 (145 Views)

Re: How to add two Datetime RANGES ?

Shalom,

I've always found the most reliable way to do date calculations is to use http://www.hpux.ws/merijn/caljd.sh and let it do calculation after using awk to extract the dates out of the file.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Please use plain text.
Frequent Advisor
Posts: 54
Registered: ‎08-03-2006
Message 4 of 9 (145 Views)

Re: How to add two Datetime RANGES ?

Steven,

I don't want the overlapping value.
I hope you are understanding me.
Please use plain text.
Acclaimed Contributor
Posts: 21,184
Registered: ‎07-06-2000
Message 5 of 9 (145 Views)

Re: How to add two Datetime RANGES ?

Hi:

I think Steve Lewis's solution is the most direct.

SEP, while Clay's 'caljd' is very useful, it doesn't addresss the need for time delta computation.

If I was presented with this data in a file, I'd use perl --- most probably the Delta_DHMS function of 'Date::Calc' available from CPAN.

Regards!

...JRF...
Please use plain text.
Frequent Advisor
Posts: 54
Registered: ‎08-03-2006
Message 6 of 9 (145 Views)

Re: How to add two Datetime RANGES ?

Steve has not taken account of overlapping values in date time.
I don't want the overlapping value in the final result.
Please use plain text.
Honored Contributor
Posts: 1,575
Registered: ‎02-10-2003
Message 7 of 9 (145 Views)

Re: How to add two Datetime RANGES ?

Hi,

if you don't want overlapping times be added,
simply check, if the end time of the first log entry is after the start time of the next.
If that is the case, use the end time of the previous line as the starting time of the next. In that way you can keep parsing line by line.

mfG Peter
The Universe is a pretty big place, it's bigger than anything anyone has ever dreamed of before. So if it's just us, seems like an awful waste of space, right? Jodie Foster in "Contact"
Please use plain text.
Honored Contributor
Posts: 6,581
Registered: ‎05-19-2003
Message 8 of 9 (145 Views)

Re: How to add two Datetime RANGES ?

This PERL script might do the trick.
Day transition NOT test, and will not work for overlap.

sub seconds {(\$h,\$m,\$s)=split /:/,@_[0]; return \$h*3600+\$m*60+\$s}
while (<>) {
(\$bd,\$bt,\$ed,\$et) = split;
\$b = &seconds (\$bt);
\$e = &seconds (\$et);
\$b -= 86400 if (\$bd lt \$ed);
\$b = \$last if (\$b < \$last);
\$last = \$e;
\$time += (\$e - \$b);
print "b=\$b, e=\$e, t=\$time\n";
}
print "total time = \$time\n";

\$ perl tmp.pl tmp.txt
b=25822, e=33086, t=7264
b=33086, e=43886, t=18064
total time = 18064

Please use plain text.
Honored Contributor
Posts: 6,581
Registered: ‎05-19-2003
Message 9 of 9 (145 Views)

Re: How to add two Datetime RANGES ?

I was close. The overlap already worked.
The following works with a simple date change.
Overlap + date change does not work.

use strict;
use warnings;
my \$time = 0;

sub seconds {my (\$h,\$m,\$s)=split /:/,\$_[0]; return \$h*3600+\$m*60+\$s}
while (<>) {
my \$last = 0;
my (\$bd,\$bt,\$ed,\$et) = split;
my \$b = &seconds (\$bt);
my \$e = &seconds (\$et);
\$b = \$last if (\$b < \$last);
\$b -= 86400 if (\$bd lt \$ed);
\$last = \$e;
# my \$elapsed = (\$e - \$b);
\$time += (\$e - \$b);
# print "b=\$b, e=\$e, elapsed=\$elapsed, t=\$time\n";
}
print "total time = \$time\n";

#cat tmp.txt
2006-07-01 07:10:22 2006-07-01 09:11:26
2006-07-01 08:10:22 2006-07-01 12:11:26
2006-07-01 23:00:00 2006-07-02 00:10:00
2006-07-02 07:50:50 2006-07-02 08:20:20

#perl tmp.pl tmp.txt
b=25822, e=33086, elapsed=7264, t=7264
b=29422, e=43886, elapsed=14464, t=21728
b=-3600, e=600, elapsed=4200, t=25928
b=28250, e=30020, elapsed=1770, t=27698
total time = 27698

Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation