Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Languages and Scripting
cancel
Showing results for 
Search instead for 
Did you mean: 

How to add two Datetime RANGES ?

Highlighted
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
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