Postgres Pro
Downloads
Home   >   mailing lists

Thread: query for records based on date

query for records based on date

From
"Brandon Metcalf"
Date:
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

Re: query for records based on date

From
Martín Marqués
Date:
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
-----------------------------------------------------------------

Re: query for records based on date

From
"Brandon Metcalf"
Date:
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
withouttimem> > zone", what is the best way to query that table for records that arem> > older than, say, one week.m>
>m>> I suppose I could get the current time in epoch seconds and the time ofm> > the attribute in the same, and select
thoserecords where the differencem> > is greater than the number of seconds in a week.  Is there somethingm> > easier
likem>>m> >   SELECT * FROM table WHERE attribute IS OLDER THAN one week; 
m> SELECT * FROM table WHERE attribute < (now()::DATE - 7)::TIMESTAMP;


Thanks.


--
Brandon

Re: query for records based on date

From
Richard Huxton
Date:
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