CODEWITHSUNDEEP
linuxperloracle11g
user3217883
user3217883

Reputation: 1463

convert Oracle DATE to linux time

I'm having trouble converting an Oracle DATE field to a linux time format in perl. I'm pulling the date field like this:

my $query = "SELECT RESPONSE_DATE FROM TABLENAME";
my $sth = $dbh->prepare($query);
$sth->execute() or die "Couldn't execute statement: " . $sth->errstr;
my @results = $sth->fetchrow_array();
printf "response_date=%s",$results[0];
printf "localtime(time)=%s",localtime(time);

Output:

response_date=14-OCT-14 08.35.00.000000 PM
localtime(time)=Tue Aug 18 23:35:13 2015

I can get all the pieces of the local time like this:

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);

But now I need to compare those pieces with similar pieces of response_date. Any ideas?

Upvotes: 2

Views: 177

Answers (1)

Borodin
Borodin

Reputation: 126732

I presume you need all the localtime fields for some sort of date calculation?

The Time::Piece module is the usual solution to this. It allows you to parse your database time and also overloads localtime to that it returns a Time::Piece object in scalar context

Unfortunately Time::Piece won't parse the fractional seconds field, so if it's not always zero then you will need to remove it from the date-time string

Clearly other calculations are possible once you have both the database value and the current date as Time::Piece objects

use strict;
use warnings;

use Time::Piece;
use Time::Seconds 'ONE_DAY';

my $s = '14-OCT-14 08.35.00.000000 PM';

my $dt = Time::Piece->strptime($s, '%d-%b-%y %H.%M.%S.000000 %p');

my $now = localtime;

printf "%s was %.3f days ago\n",
        $dt->strftime('%a %d-%b-%Y at %H:%M:%S'),
        ($now - $dt)->days;

output

Tue 14-Oct-2014 at 20:35:00 was 308.190 days ago

Upvotes: 1

Related Questions