Thread: date comparison between perl and postgres

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

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

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)