pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Subtracting from a date
4+ messages / 3 participants
[nested] [flat]

* Subtracting from a date
@ 2000-04-12 17:46 Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
  2000-04-13 09:28 ` Re: Subtracting from a date Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  0 siblings, 1 reply; 4+ messages in thread

From: Andrew Perrin - Demography @ 2000-04-12 17:46 UTC (permalink / raw)
  To: pgsql-sql

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




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

* Re: Subtracting from a date
  2000-04-12 17:46 Subtracting from a date Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
@ 2000-04-13 09:28 ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  2000-04-13 14:06   ` Re: Subtracting from a date Andy Lewis <alewis@recruitersonline.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Moray McConnachie @ 2000-04-13 09:28 UTC (permalink / raw)
  To: Andrew Perrin - Demography <aperrin@demog.berkeley.edu>; pgsql-sql

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




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

* Re: Subtracting from a date
  2000-04-12 17:46 Subtracting from a date Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
  2000-04-13 09:28 ` Re: Subtracting from a date Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
@ 2000-04-13 14:06   ` Andy Lewis <alewis@recruitersonline.com>
  2000-04-13 14:51     ` Re: Subtracting from a date Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  0 siblings, 1 reply; 4+ messages in thread

From: Andy Lewis @ 2000-04-13 14:06 UTC (permalink / raw)
  To: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>; +Cc: Andrew Perrin - Demography <aperrin@demog.berkeley.edu>; pgsql-sql

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




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

* Re: Subtracting from a date
  2000-04-12 17:46 Subtracting from a date Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
  2000-04-13 09:28 ` Re: Subtracting from a date Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  2000-04-13 14:06   ` Re: Subtracting from a date Andy Lewis <alewis@recruitersonline.com>
@ 2000-04-13 14:51     ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  0 siblings, 0 replies; 4+ messages in thread

From: Moray McConnachie @ 2000-04-13 14:51 UTC (permalink / raw)
  To: Andy Lewis <alewis@recruitersonline.com>; +Cc: Andrew Perrin - Demography <aperrin@demog.berkeley.edu>; pgsql-sql

> 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




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


end of thread, other threads:[~2000-04-13 14:51 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-12 17:46 Subtracting from a date Andrew Perrin - Demography <aperrin@demog.berkeley.edu>
2000-04-13 09:28 ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
2000-04-13 14:06   ` Andy Lewis <alewis@recruitersonline.com>
2000-04-13 14:51     ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>

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