Thread: Need Help With Dates.

Hello.
I just migrated a database from MySQL to postgreSQL and am having trouble
wit postgres' dates.

MySQL dealt with dates very well, but i don't see the same sort of
functionality in postgres.

The database is an archive of imformation, and i would like to do a cron'd
select for an interval based on the date.
I can get the current date.  But i don't know how to have the computer
properly figure out the past dates.

The select format has been:    SELECT blah FROM blah2        WHERE date BETWEEN (past_date) and (current_date);

This select is computed monthly.
And i do not want to have to change the variables every month when this
needs to run.  Nor do i think that i should have to result to perl
processing to solve this dilemma.  I have tried (i think) every possible
function and operation to try to get this to work.

The problem is trying to figure out whether an extra day should be added
for leap years. (It obviously should, but how do i tell the computer that
it should).  Postgres does not seem to recognize that concept well.

Since this is running monthly, if you have any ideas to form a select
like:SELECT _ WHERE date BETWEEN (date - 12 months) and ...
in other words, since postgres increments by day . . . is there anyway to get it to allow you to increment / decrement
by
month?

thanks in advance.
also for reading this (long winded) post.
.jtp

> I just migrated a database from MySQL to postgreSQL and am having trouble
> wit postgres' dates.
> MySQL dealt with dates very well, but i don't see the same sort of
> functionality in postgres.

??

> The database is an archive of imformation, and i would like to do a cron'd
> select for an interval based on the date.
> I can get the current date.  But i don't know how to have the computer
> properly figure out the past dates.
> 
> The select format has been:
>      SELECT blah FROM blah2
>         WHERE date BETWEEN (past_date) and (current_date);
> This select is computed monthly.
> And i do not want to have to change the variables every month when this
> needs to run.  Nor do i think that i should have to result to perl
> processing to solve this dilemma.  I have tried (i think) every possible
> function and operation to try to get this to work.

It is not clear to me *exactly* what query you used to run. Were
"past_date" and "current_date" some local program variable in the MySQL
front end? How did you set them in a way which required no external
programming or variable substitution?

> The problem is trying to figure out whether an extra day should be added
> for leap years. (It obviously should, but how do i tell the computer that
> it should).

The computer already knows. How about
 select * from t1 where d between   (date_trunc('month', date 'today') - interval '1 month')   and
date_trunc('month',date 'today');
 

There are *lots* of date/time capabilities in Postgres (if I do say so
myself ;) so I'd be suprised if you don't find what you need.

Good luck.
                    - Thomas