How to add two Datetime RANGES ? (288 Views)
Reply
Frequent Advisor
Pankaj Yadav_1
Posts: 54
Registered: ‎08-03-2006
Message 1 of 9 (288 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.
Honored Contributor
Steve Lewis
Posts: 725
Registered: ‎05-18-2000
Message 2 of 9 (288 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

Exalted Contributor
Steven E. Protter
Posts: 33,806
Registered: ‎08-15-2002
Message 3 of 9 (288 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
Frequent Advisor
Pankaj Yadav_1
Posts: 54
Registered: ‎08-03-2006
Message 4 of 9 (288 Views)

Re: How to add two Datetime RANGES ?

Steven,

I don't want the overlapping value.
I hope you are understanding me.
Acclaimed Contributor
James R. Ferguson
Posts: 21,184
Registered: ‎07-06-2000
Message 5 of 9 (288 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...
Frequent Advisor
Pankaj Yadav_1
Posts: 54
Registered: ‎08-03-2006
Message 6 of 9 (288 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.
Honored Contributor
Peter Nikitka
Posts: 1,575
Registered: ‎02-10-2003
Message 7 of 9 (288 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"
Honored Contributor
Hein van den Heuvel
Posts: 6,588
Registered: ‎05-19-2003
Message 8 of 9 (288 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


Honored Contributor
Hein van den Heuvel
Posts: 6,588
Registered: ‎05-19-2003
Message 9 of 9 (288 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


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.