Thread
-
Query based on date/time field
William McCracken <will@sitestar.com> — 1998-08-04T14:09:31Z
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. Thanks, Will McCracken
-
Re: [SQL] Query based on date/time field
Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-08-12T12:25:39Z
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 -
Re: [SQL] Query based on date/time field
Aleksey Dashevsky <postgres@luckynet.co.il> — 1998-08-12T12:47:45Z
I just wanted to answer the initial question, but Heruoth managed to do so before me:-) Anyway, I decided to play with datetime and timespan types and was stuck with strange problem. Here it is: If I subtract one datetime from another everithing is going as expected: userbase=> select '12 Jun 98'::datetime-'11 Jun 98'::datetime; ?column? -------- @ 1 day (1 row) But, subtracting one date from another one I got something like int: userbase=> select '12 Jun 98'::date-'11 Jun 98'::date; ?column? -------- 1 (1 row) The next query explains more clearly that type of result is "int4": userbase=> select ('12 Jun 98'::date-'11 Jun 98'::date)::datetime; ERROR: function datetime(int4) does not exist Is that what is expected to be or a bug?? ( I'm running PostgreSQL 6.3.2p1-4 on Linux (SlackWare) 2.0.34 ) On Wed, 12 Aug 1998, Herouth Maoz wrote: > 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 > > > -
Re: [SQL] Query based on date/time field
Holger Mitterwald <mittehlg@coi.de> — 1998-08-12T12:50:02Z
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. So long, Holger -------------------------------------------------------------------------------- Holger Mitterwald COI-D #### ##### ### CONSULTING FUER OFFICE COI GmbH Herzogenaurach # # # # UND INFORMATION Industriestrasse 1 - 3 # # # # MANAGEMENT GmbH D-91074 Herzogenaurach #### ##### ### Tel.: 09132/82-3811