Postgres Pro
Downloads
Home   >   mailing lists

Thread: comparring dates between perl and postgres

comparring dates between perl and postgres

From
hodges@xprt.net
Date:
I am getting a field of date type from PostgreSQL and an
integer that is a number of days (1 to 3).

select date, num_days from table
$date, $num_days

In my perl script I am getting the current date

#find current date
($mday,$mon,$year,$doy)=(localtime(time))[3..5,7];
$mon ++;
$year +=1900;
$today = "$year-$mon-$mday";

I need to test whether $date from PostgreSQL matches
$today + $num_days.

Can perl extract day of year ($doy) from the Postgres date?

Is there a better way to do this?

Thanks,
Tom Hodges

Re: comparring dates between perl and postgres

From
Frank Way
Date:
If you are using the Perl Date::Calc module (available from CPAN) you
should be able to plug the year, month, day fields (that you can fairly
easily extract from the date returned by PostgreSQL) into the
Day_of_Year function and get what you want.

Hope this helps,
Frank Way


--- hodges@xprt.net wrote:
> I am getting a field of date type from PostgreSQL and an
> integer that is a number of days (1 to 3).
>
> select date, num_days from table
> $date, $num_days
>
> In my perl script I am getting the current date
>
> #find current date
> ($mday,$mon,$year,$doy)=(localtime(time))[3..5,7];
> $mon ++;
> $year +=1900;
> $today = "$year-$mon-$mday";
>
> I need to test whether $date from PostgreSQL matches
> $today + $num_days.
>
> Can perl extract day of year ($doy) from the Postgres date?
>
> Is there a better way to do this?
>
> Thanks,
> Tom Hodges
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


=====
Frank Way
E-Mail: fgw_three@yahoo.com

"Place guards on all the roads and keep the troops
from running to the rear..." MG John Buford, 1863

__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

Re: comparring dates between perl and postgres

From
SaiHertz And Control Systems
Date:
hodges@xprt.net wrote:

>I am getting a field of date type from PostgreSQL and an
>integer that is a number of days (1 to 3).
>
>select date, num_days from table
>$date, $num_days
>
>In my perl script I am getting the current date
>
>#find current date
>($mday,$mon,$year,$doy)=(localtime(time))[3..5,7];
>$mon ++;
>$year +=1900;
>$today = "$year-$mon-$mday";
>
>

I dont know about perl but Postgresql will do it for you as
mypromt:#SELECT EXTRACT(DOY FROM DATE '2003-12-25')
date_part
----------------------
359

1 Row(s) affected

Shoot back if this helps !

Regards,
V Kashyap

Re: comparring dates between perl and postgres

From
hodges@xprt.net
Date:
I have never used extract.  This generates a syntax error when I added
and select extract( DOY FROM date)

    $sth = $dbh->prepare("SELECT date,address,time,host,direction,description,
       cal,reminder from firesides and select extract( DOY FROM date)
       where date > '$dstr1' and date <= '$dstr2'");
    if ($sth->execute)
    {
# assign each query record to an array
        $numrow = 0;
     while
        (($date,$address,$time,$host,$directions,$description,$cal,$reminder,
$db_doy) = $sth->fetc\
hrow)

Tom

On 4 Dec 2003 at 2:56, SaiHertz And Control Systems wrote:

> hodges@xprt.net wrote:
>
> >I am getting a field of date type from PostgreSQL and an
> >integer that is a number of days (1 to 3).
> >
> >select date, num_days from table
> >$date, $num_days
> >
> >In my perl script I am getting the current date
> >
> >#find current date
> >($mday,$mon,$year,$doy)=(localtime(time))[3..5,7];
> >$mon ++;
> >$year +=1900;
> >$today = "$year-$mon-$mday";
> >
> >
>
> I dont know about perl but Postgresql will do it for you as
> mypromt:#SELECT EXTRACT(DOY FROM DATE '2003-12-25')
> date_part
> ----------------------
> 359
>
> 1 Row(s) affected
>
> Shoot back if this helps !
>
> Regards,
> V Kashyap
>
>
>
>
>

Re: comparring dates between perl and postgres

From
Sai Hertz And Control Systems
Date:
I have never used extract. This generates a syntax error when I added

>and select extract( DOY FROM date)
>
>    $sth = $dbh->prepare("SELECT date,address,time,host,direction,description,
>       cal,reminder from firesides and select extract( DOY FROM date)
>       where date > '$dstr1' and date <= '$dstr2'");
>    if ($sth->execute)
>    {
># assign each query record to an array
>        $numrow = 0;
>     while
>        (($date,$address,$time,$host,$directions,$description,$cal,$reminder,
>$db_doy) = $sth->fetc\
>hrow)
>
>
>
As per me the error is quite evident  because  it must be some thing
like this

select extract(DOY FROM date'COLUMN_FIELD')

CREATE TABLE test_date (
  "id" BIGSERIAL,
  "exp_date" DATE,
  "comp_int" int,
  PRIMARY KEY("id")
)

add some data
and do as

prompt=# select comp_int as Compared_Integer , extract( DOY from
exp_date) as Compared_Date from test_date where  id = 1 ;
 compared_integer | compared_date
------------------+---------------
                1 |           335
(1 row)

this may gve you results .

Regards,
V kashyap

Re: comparring dates between perl and postgres

From
Josh Berkus
Date:
Hodges,

> I have never used extract.  This generates a syntax error when I added
> and select extract( DOY FROM date)

Posting the syntax error would be useful if you want help.

>     $sth = $dbh->prepare("SELECT
> date,address,time,host,direction,description, cal,reminder from firesides
> and select extract( DOY FROM date) where date > '$dstr1' and date <=
> '$dstr2'");

One problem is that you've used a reserved word as a column name: "date".
PostgreSQL is probably getting confused between "date" the column and "date"
the data type.   Try double quoting date:  "date" (or escaped for perl:
\"date\")

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: comparring dates between perl and postgres

From
greg@turnstep.com
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Is there a better way to do this?

In general, it is better to do all of your date and time calculation
in one area. Since you are storing dates in the database, that is
where you should attempt to do everything. Not only is it cleaner and
easier (once you learn some of the time/date manipulation syntax), but
there is no guarantee that perl's notion of "now" is the same as the
database's concept of "now" (i.e. the perl script and the database may
be on different systems).

In specific, you can convert the date to an integer and use that and
your numdays column to make the comparison. Using the DOY may look good:

SELECT * FROM time4timer WHERE
  EXTRACT(DOY FROM mydate) = EXTRACT(DOY FROM CURRENT_DATE) + numdays;

...but it fails at the edges of the year. (December 31 DOY + 1 is not 1).

What you really want is simply:

SELECT * FROM time4timer WHERE mydate = CURRENT_DATE + numdays;

I should point out that this works because of a few glossed over points:
adding an integer to CURRENT_DATE always implies a number of days by
default (as opposed to another unit of time), and 'mydate' must be of
type 'date' (a timestamp would need to be explicitly cast as a date for
the match to work).

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200312141512

-----BEGIN PGP SIGNATURE-----

iD8DBQE/3MTkvJuQZxSWSsgRAtrdAJ9cHbYWhOSWSmObak+xiZccF3+4AgCgka+5
UyIiYthyXooFjRLMv89gFaw=
=SG9w
-----END PGP SIGNATURE-----