Received: from lubitsch.akademie.de (ns.akademie.de [62.165.4.3]) by postgresql.org (Postfix) with ESMTP id 2D4194762FE for ; Tue, 12 Mar 2002 12:28:14 -0500 (EST) Received: from [62.165.4.160] (helo=redgrave) by lubitsch.akademie.de with smtp (Exim 3.33 #2) id 16kq4N-0001zN-00; Tue, 12 Mar 2002 18:28:15 +0100 From: Ian Barwick Organization: akademie.de asp GmbH Date: Tue, 12 Mar 2002 18:28:08 +0100 X-Mailer: KMail [version 1.1.99] Content-Type: text/plain; charset="iso-8859-1" To: Gary Stainburn , "pgsql-sql" References: <02031214251401.06708@redgrave> In-Reply-To: Subject: Re: in-date news items MIME-Version: 1.0 Message-Id: <02031218280806.06708@redgrave> Content-Transfer-Encoding: 8bit X-Archive-Number: 200203/167 X-Sequence-Number: 7047 On Tuesday 12 March 2002 18:19, Gary Stainburn wrote: > On Tuesday 12 March 2002 1:25 pm, Ian Barwick wrote: > > On Tuesday 12 March 2002 13:17, Gary Stainburn wrote: > > > Hi all, > > > > > > I'm trying to use a select to get the latest item of news, provided > > > that it's within the last X days. What do I need to add to the where > > > clause for the select statement below: > > > > > > select ndate, nauthor, nheading > > > from news > > > order by ndate desc > > > limit 1 > > > *** where ndate within the last 3 months ****** > > > ; > > > > something like: > > > > where ndate > current_timestamp - interval '3 months' > > > > more on the subject here: > > > > http://techdocs.postgresql.org/techdocs/faqdatesintervals.php > > > > Ian Barwick > > Thanks for that Ian, worked a treat. However, can someone tell me why > > select n.ntimestamp, n.nmid from news n where n.ntimestamp > current_time - > interval '5 minutes'; > > works while > > select n.ntimestamp, n.nmid, m.mfname, m.mlname > from news n, members m > order by ntimestamp desc > limit 1 > where (n.nmid = m.mid) and > (n.ntimestamp > current_time - interval '5 minutes'); > > has a parse error at or near "where" select n.ntimestamp, n.nmid, m.mfname, m.mlname from news n, members m where (n.nmid = m.mid) and (n.ntimestamp > current_time - interval '5 minutes') order by ntimestamp desc limit 1 should work... Ian Barwick