pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquery for records based on date
4+ messages / 3 participants
[nested] [flat]
* query for records based on date
@ 2005-02-21 20:00 Brandon Metcalf <bmetcalf@nortel.com>
0 siblings, 2 replies; 4+ messages in thread
From: Brandon Metcalf @ 2005-02-21 20:00 UTC (permalink / raw)
To: pgsql-sql
If I have a table with an attribute of type "timestamp without time
zone", what is the best way to query that table for records that are
older than, say, one week.
I suppose I could get the current time in epoch seconds and the time of
the attribute in the same, and select those records where the difference
is greater than the number of seconds in a week. Is there something
easier like
SELECT * FROM table WHERE attribute IS OLDER THAN one week;
Thanks.
--
Brandon
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: query for records based on date
@ 2005-02-21 20:42 Martín Marqués <martin@bugs.unl.edu.ar>
parent: Brandon Metcalf <bmetcalf@nortel.com>
1 sibling, 1 reply; 4+ messages in thread
From: Martín Marqués @ 2005-02-21 20:42 UTC (permalink / raw)
To: Brandon Metcalf <bmetcalf@nortel.com>; +Cc: pgsql-sql
El Lun 21 Feb 2005 17:00, Brandon Metcalf escribió:
> If I have a table with an attribute of type "timestamp without time
> zone", what is the best way to query that table for records that are
> older than, say, one week.
>
> I suppose I could get the current time in epoch seconds and the time of
> the attribute in the same, and select those records where the difference
> is greater than the number of seconds in a week. Is there something
> easier like
>
> SELECT * FROM table WHERE attribute IS OLDER THAN one week;
SELECT * FROM table WHERE attribute < (now()::DATE - 7)::TIMESTAMP;
--
17:41:37 up 162 days, 7:36, 2 users, load average: 0.11, 0.12, 0.08
-----------------------------------------------------------------
Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: query for records based on date
@ 2005-02-21 20:48 Brandon Metcalf <bmetcalf@nortel.com>
parent: Martín Marqués <martin@bugs.unl.edu.ar>
0 siblings, 0 replies; 4+ messages in thread
From: Brandon Metcalf @ 2005-02-21 20:48 UTC (permalink / raw)
To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
m == martin@bugs.unl.edu.ar writes:
m> El Lun 21 Feb 2005 17:00, Brandon Metcalf escribió:
m> > If I have a table with an attribute of type "timestamp without time
m> > zone", what is the best way to query that table for records that are
m> > older than, say, one week.
m> >
m> > I suppose I could get the current time in epoch seconds and the time of
m> > the attribute in the same, and select those records where the difference
m> > is greater than the number of seconds in a week. Is there something
m> > easier like
m> >
m> > SELECT * FROM table WHERE attribute IS OLDER THAN one week;
m> SELECT * FROM table WHERE attribute < (now()::DATE - 7)::TIMESTAMP;
Thanks.
--
Brandon
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: query for records based on date
@ 2005-02-21 20:53 Richard Huxton <dev@archonet.com>
parent: Brandon Metcalf <bmetcalf@nortel.com>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2005-02-21 20:53 UTC (permalink / raw)
To: Brandon Metcalf <bmetcalf@nortel.com>; +Cc: pgsql-sql
Brandon Metcalf wrote:
> If I have a table with an attribute of type "timestamp without time
> zone", what is the best way to query that table for records that are
> older than, say, one week.
>
> I suppose I could get the current time in epoch seconds and the time of
> the attribute in the same, and select those records where the difference
> is greater than the number of seconds in a week. Is there something
> easier like
>
> SELECT * FROM table WHERE attribute IS OLDER THAN one week;
Look up "intervals" in the manuals - you can just subtract timestamps/dates.
Probably also worth searching the mailing lists for "ago()" and
"volatile" to learn about the issues with indexing.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-02-21 20:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-21 20:00 query for records based on date Brandon Metcalf <bmetcalf@nortel.com>
2005-02-21 20:42 ` Martín Marqués <martin@bugs.unl.edu.ar>
2005-02-21 20:48 ` Brandon Metcalf <bmetcalf@nortel.com>
2005-02-21 20:53 ` Richard Huxton <dev@archonet.com>
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