Thread: date comparison between perl and postgres

date comparison between perl and postgres

From
hodges@xprt.net
Date:
It seems that perls local::time function returns the day
of the month as 1-31 whereas postgres returns it as 01-31.

I need to compare dates

if 2004-1-12 (from perl) less than 2004-01-19 (from postgres) and its failing.

Is there a way to have perl return day of the month as
a 2 digit string or some code to automaticly pad it?
I am using

# Get current time and date
($mday,$mon,$year)=(localtime(time + (24*60*60)))[3..5];
$mon ++;
$year +=1900;
$dstr1 = "$year-$mon-$mday";

Thanks for any ideas,

Tom Hodges
hodges@xprt.net

Re: date comparison between perl and postgres

From
hodges@xprt.net
Date:
To correct my self - that's month that needs to be padded
with the leading zero.

Tom

On 11 Jan 2004 at 9:18, hodges@xprt.net wrote:

> It seems that perls local::time function returns the day
> of the month as 1-31 whereas postgres returns it as 01-31.
>
> I need to compare dates
>
> if 2004-1-12 (from perl) less than 2004-01-19 (from postgres) and its failing.
>
> Is there a way to have perl return day of the month as
> a 2 digit string or some code to automaticly pad it?
> I am using
>
> # Get current time and date
> ($mday,$mon,$year)=(localtime(time + (24*60*60)))[3..5];
> $mon ++;
> $year +=1900;
> $dstr1 = "$year-$mon-$mday";
>
> Thanks for any ideas,
>
> Tom Hodges
> hodges@xprt.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>



Re: date comparison between perl and postgres

From
Bruno Wolff III
Date:
On Sun, Jan 11, 2004 at 09:27:09 -0800,
  hodges@xprt.net wrote:
> > Is there a way to have perl return day of the month as
> > a 2 digit string or some code to automaticly pad it?
> > I am using
> >
> > # Get current time and date
> > ($mday,$mon,$year)=(localtime(time + (24*60*60)))[3..5];
> > $mon ++;
> > $year +=1900;
> > $dstr1 = "$year-$mon-$mday";
> >
> > Thanks for any ideas,

You could use sprintf in perl to generate the date string.

If you are actually doing the compare in postgres it might be better
to just get the time in seconds since the epoch and cast that to abstime
and then to date.
For example:
area=> select 1073921933::abstime::date;
    date
------------
 2004-01-12
(1 row)