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 (AT) postgresql (DOT) org

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 (AT) xprt (DOT) net wrote:


=====
Frank Way
E-Mail: fgw_three (AT) yahoo (DOT) 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/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

hodges (AT) xprt (DOT) net wrote:

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






---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

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,$descript ion,$cal,$reminder,
$db_doy) = $sth->fetc\
hrow)

Tom

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



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

I have never used extract. This generates a syntax error when I added

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





---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Hodges,

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

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


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 (AT) turnstep (DOT) com
PGP Key: 0x14964AC8 200312141512

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

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



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend