topic Re: How to add two Datetime RANGES ? in Operating System - Linux

How to add two Datetime RANGES ?

Pankaj Yadav_1 — Mon, 21 Aug 2006 02:09:35 GMT

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.

Re: How to add two Datetime RANGES ?

Steve Lewis — Mon, 21 Aug 2006 03:35:37 GMT

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

Re: How to add two Datetime RANGES ?

Steven E. Protter — Mon, 21 Aug 2006 03:46:23 GMT

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

Re: How to add two Datetime RANGES ?

Pankaj Yadav_1 — Mon, 21 Aug 2006 04:22:47 GMT

Steven,

I don't want the overlapping value.
I hope you are understanding me.

Re: How to add two Datetime RANGES ?

James R. Ferguson — Mon, 21 Aug 2006 06:57:50 GMT

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

Re: How to add two Datetime RANGES ?

Pankaj Yadav_1 — Mon, 21 Aug 2006 07:14:00 GMT

Steve has not taken account of overlapping values in date time.
I don't want the overlapping value in the final result.

Re: How to add two Datetime RANGES ?

Peter Nikitka — Mon, 21 Aug 2006 07:32:12 GMT

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

Re: How to add two Datetime RANGES ?

Hein van den Heuvel — Mon, 21 Aug 2006 07:55:49 GMT

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

Re: How to add two Datetime RANGES ?

Hein van den Heuvel — Mon, 21 Aug 2006 09:01:25 GMT

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