- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - Linux
- >
- How to add two Datetime RANGES ?
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-20-2006 07:09 PM
тАО08-20-2006 07:09 PM
How to add two Datetime RANGES ?
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.
- Tags:
- date arithmetic
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-20-2006 08:35 PM
тАО08-20-2006 08:35 PM
Re: How to add two Datetime RANGES ?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-20-2006 08:46 PM
тАО08-20-2006 08:46 PM
Re: How to add two Datetime RANGES ?
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
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Tags:
- caljd
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-20-2006 09:22 PM
тАО08-20-2006 09:22 PM
Re: How to add two Datetime RANGES ?
I don't want the overlapping value.
I hope you are understanding me.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-20-2006 11:57 PM
тАО08-20-2006 11:57 PM
Re: How to add two Datetime RANGES ?
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...
- Tags:
- Perl
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-21-2006 12:14 AM
тАО08-21-2006 12:14 AM
Re: How to add two Datetime RANGES ?
I don't want the overlapping value in the final result.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-21-2006 12:32 AM
тАО08-21-2006 12:32 AM
Re: How to add two Datetime RANGES ?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-21-2006 12:55 AM
тАО08-21-2006 12:55 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-21-2006 02:01 AM
тАО08-21-2006 02:01 AM
Re: How to add two Datetime RANGES ?
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