> 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
