Re: comparring dates between perl and postgres

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