pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Need Help With Dates.
2+ messages / 2 participants
[nested] [flat]

* Need Help With Dates.
@ 2000-07-03 17:40 John <john@akadine.com>
  2000-07-03 18:11 ` Re: Need Help With Dates. Thomas Lockhart <lockhart@alumni.caltech.edu>
  0 siblings, 1 reply; 2+ messages in thread

From: John @ 2000-07-03 17:40 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Need Help With Dates.
  2000-07-03 17:40 Need Help With Dates. John <john@akadine.com>
@ 2000-07-03 18:11 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
  0 siblings, 0 replies; 2+ messages in thread

From: Thomas Lockhart @ 2000-07-03 18:11 UTC (permalink / raw)
  To: John <john@akadine.com>; +Cc: pgsql-sql

> 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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-07-03 18:11 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-03 17:40 Need Help With Dates. John <john@akadine.com>
2000-07-03 18:11 ` Thomas Lockhart <lockhart@alumni.caltech.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox