Message-ID: <421A4A45.7090309@archonet.com>
Date: Mon, 21 Feb 2005 20:53:25 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Brandon Metcalf <bmetcalf@nortel.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: query for records based on date
References: <Pine.LNX.4.58L.0502211356580.24449@cash.rhiamet.com>
In-Reply-To: <Pine.LNX.4.58L.0502211356580.24449@cash.rhiamet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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