Обсуждение: comparring dates between perl and postgres
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
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/
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
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
>
>
>
>
>
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
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
-----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-----