Thread: Subtracting from a date
I need to find all records with a date field (date_effective) 3 years or less prior to now(). This clearly must be possible, but I'm not figuring it out. Any suggestions? --------------------------------------------------------------------- Andrew J. Perrin - aperrin@demog.berkeley.edu - NT/Unix Admin/Support Department of Demography - University of California at Berkeley 2232 Piedmont Avenue #2120 - Berkeley, California, 94720-2120 USA http://demog.berkeley.edu/~aperrin --------------------------SEIU1199
WHERE age('now',date_effective::datetime)<='3 years'::timespan I don't know if 'now' in that context is still the authoritative way to write now, maybe it should be WHERE age(now(),date_effective::datetime)<='3 years'::timespan if date_effective is actually a datetime field, which I understand we are moving to in 7.0 (date fields being non-SQL-standard), then you don't need the cast, obviously. ---------------------------------------------------------------- Moray.McConnachie@computing-services.oxford.ac.uk ----- Original Message ----- From: "Andrew Perrin - Demography" <aperrin@demog.berkeley.edu> To: <pgsql-sql@postgresql.org> Sent: Wednesday, April 12, 2000 6:46 PM Subject: [SQL] Subtracting from a date > I need to find all records with a date field (date_effective) 3 years or > less prior to now(). This clearly must be possible, but I'm not figuring > it out. Any suggestions? > > --------------------------------------------------------------------- > Andrew J. Perrin - aperrin@demog.berkeley.edu - NT/Unix Admin/Support > Department of Demography - University of California at Berkeley > 2232 Piedmont Avenue #2120 - Berkeley, California, 94720-2120 USA > http://demog.berkeley.edu/~aperrin --------------------------SEIU1199 > >
What would be wrong with: SELECT date_effective FROM your_table WHERE date_effective <= (now() + '3 years ago') Certainly looks alittle mmore readable. Andy On Thu, 13 Apr 2000, Moray McConnachie wrote: > WHERE age('now',date_effective::datetime)<='3 years'::timespan > > I don't know if 'now' in that context is still the authoritative way to > write now, maybe it should be > > WHERE age(now(),date_effective::datetime)<='3 years'::timespan > > if date_effective is actually a datetime field, which I understand we are > moving to in 7.0 (date fields being non-SQL-standard), then you don't need > the cast, obviously. > ---------------------------------------------------------------- > Moray.McConnachie@computing-services.oxford.ac.uk > > ----- Original Message ----- > From: "Andrew Perrin - Demography" <aperrin@demog.berkeley.edu> > To: <pgsql-sql@postgresql.org> > Sent: Wednesday, April 12, 2000 6:46 PM > Subject: [SQL] Subtracting from a date > > > > I need to find all records with a date field (date_effective) 3 years or > > less prior to now(). This clearly must be possible, but I'm not figuring > > it out. Any suggestions? > > > > --------------------------------------------------------------------- > > Andrew J. Perrin - aperrin@demog.berkeley.edu - NT/Unix Admin/Support > > Department of Demography - University of California at Berkeley > > 2232 Piedmont Avenue #2120 - Berkeley, California, 94720-2120 USA > > http://demog.berkeley.edu/~aperrin --------------------------SEIU1199 > > > > >
> SELECT date_effective FROM your_table WHERE date_effective <= (now() + '3 years ago') That looks good. If date_effective is a datetime - should be date_effective::datetime if as Andrew said date_effective is a date field, I guess. Also your inequality is the wrong way round for things less than or equal to 3 years old, which is what I thought Andrew wanted. I think it's a bit faster than the age() construction, too. ---------------------------------------------------------------- Moray.McConnachie@computing-services.oxford.ac.uk