Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.8.8/8.7.5) with ESMTP id IAA00111 for ; Wed, 12 Aug 1998 08:25:05 -0400 (EDT) Received: from [147.233.159.109] (herouth@mac-herouth.pc.openu.ac.il [147.233.159.109]) by shaked.cc.openu.ac.il (8.8.5/8.8.5) with ESMTP id PAA08868 for ; Wed, 12 Aug 1998 15:24:52 +0300 (IDT) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: References: <000601bdbfb1$80503a60$1304e426@loopy.sitestar.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Wed, 12 Aug 1998 15:25:39 +0300 To: pgsql-sql@postgreSQL.org From: Herouth Maoz Subject: Re: [SQL] Query based on date/time field At 15:50 +0300 on 12/8/98, Holger Mitterwald wrote: > On Tue, 4 Aug 1998, William McCracken wrote: > > > I am new to SQL and was wondering how I would go > > about selecting records from my database based on > > the age of a date/time stamp. > > > > For example if I wanted to select records older than > > 12 hours or 1 day. > > > > Any help would be appreciated. > > Sorry for my late response, but I was on holiday. > I use some statement like this to select entries that are not older than 2 > weeks: > > SELECT DISTINCT landkreis FROM kneipe WHERE 1209600 >= (date_part('epoch', > current_date) - date_part('epoch',datum)); > > datum is of type datetime. > > what I do is the following: epoch returns the seconds since january 1st > 1970 (I think). I substract the timestamp of each record from the current > timestamp (current_date). The result has to be smaller than the duration > for 2 weeks (in seconds, which is 1209600s). > > I dont know if there is a better solution, but it works fine. I think the following is simpler and more intuitive: SELECT * FROM the_table WHERE the_date >= ('now'::datetime - '12 hours'::timespan); This assumes that the field "the_date" in "the_table" is of type datetime. If it isn't, just convert it by using datetime( the_date ). The amount of time described by the type timespan is very intuitive - it accepts, as you see, things like '12 hours', '1 day', or '2 months'. It takes months correctly - with longer and shorter months taken into accounts. More about it in the pgbuiltin manpage. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma