Shell Programming and Scripting

Hi.,

After retrieving values from DB I have two datestamps in format:

Any quick solution to get the difference of two in the format : 1 day(s) 12:20:00

Thanks.,

I think that previous posts determined that you do not have GNU "date".
What Operating System are you using?
What Shell are you using?
Do you have "perl"?

Somebody here may give you a simple subtraction algorithm -- which will fail sometimes. What you need is something more robust.

What DBMS are you using? - many of them provide date subtraction that works reliably.
Plus you can get date/times in formats like Julian days which are floating point numbers.
You can use bc to do the arithmetic operation on these numbers

The reason for waffling is that you have to convert both dates into something like epoch seconds, then subtract, and return days, hours, minutes & seconds. You can do this in perl. It is just loads easier inside most modern dbms. Plus your perl may not have DateTime::Format::strptime.

In perl you can use somethin like this:

Code:

#!/usr/bin/perl

use Time::Piece;
use POSIX qw(strftime);

$before = Time::Piece->strptime("12/01/2010:05:40:00 AM", "%d/%m/%Y:%I:%M:%S %p"); 
$after  = Time::Piece->strptime("12/01/2010:06:00:00 PM", "%d/%m/%Y:%I:%M:%S %p");
$secdiff = $after - $before;
print  strftime( '%d %H:%M:%S', gmtime(int($secdiff)))."\n";;

If feasible, fetch the two dates as well as their difference from the database itself. That way you prevent the problem from becoming a bigger problem.

tyler_durden

Hi.,

I tried solution suggested by "Klashxx". But it is resulting in following error:
I do have perl utility in Unix shell (bash) I am using at /usr/bin/perl.
Flavor of unix is Linux. And the DB used in Oracle 10g.

Pl. suggest.

Do it through the DB , ..something to start:

Code:

select cast(
    NUMTODSINTERVAL(
       to_date('12/01/2010:06:00:00 PM','MM/DD/YYYY:HH12:MI:SS PM')-to_date('12/01/2010:05:40:00 AM','MM/DD/YYYY:HH12:MI:SS AM')
    ,'DAY')
as interval day(2) to second(0) ) from dual

Result:

Code:

+00 12:20:00