Operating System - Linux
1748252 Members
3873 Online
108760 Solutions
New Discussion юеВ

How to add two Datetime RANGES ?

 
Pankaj Yadav_1
Frequent Advisor

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.
8 REPLIES 8
Steve Lewis
Honored Contributor

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

Steven E. Protter
Exalted Contributor

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
Pankaj Yadav_1
Frequent Advisor

Re: How to add two Datetime RANGES ?

Steven,

I don't want the overlapping value.
I hope you are understanding me.
James R. Ferguson
Acclaimed Contributor

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...
Pankaj Yadav_1
Frequent Advisor

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.
Peter Nikitka
Honored Contributor

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"
Hein van den Heuvel
Honored Contributor

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


Hein van den Heuvel
Honored Contributor

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