From pere@hungry.com Thu Jun 18 17:27:23 1998 Received: from yuubin.games.no (yuubin.link.no [193.213.36.89]) by hub.org (8.8.8/8.7.5) with ESMTP id NAA00427 for ; Thu, 18 Jun 1998 13:27:23 -0400 (EDT) Received: from sleeper.games.no (pere@sleeper.link.no [193.213.36.74]) by yuubin.games.no (8.8.7/8.8.7) with ESMTP id TAA27964; Thu, 18 Jun 1998 19:26:27 +0200 Received: (from pere@localhost) by sleeper.games.no (8.8.7/8.8.7) id TAA25534; Thu, 18 Jun 1998 19:26:24 +0200 Date: Thu, 18 Jun 1998 19:26:24 +0200 Message-Id: <199806181726.TAA25534@sleeper.games.no> To: pgsql-sql@postgreSQL.org Followup-to: pere@games.no,pgsql-sql@postgreSQL.org Subject: 'now - 4 hours ago' ? From: Petter Reinholdtsen Reply-to: pere@hungry.com I'm still learning SQL. :-) Now I have upgraded my PostgreSQL database to v6.3.2, and have the following table. CREATE TABLE testdate( received timestamp DEFAULT CURRENT_DATE, info text NOT NULL ); I want to select the info received (inserted) more then 4 hours ago. How do I specify that in SQL? Is timestamp the best datatype for this kind of query? Which date-types exists in PostgreSQL, and what is the difference between them. I'll be very happy to get pointers to good intros. :-) I'm not on this list, so please send copies to me. -- ##> Petter Reinholdtsen <## | pere@td.org.uit.no O- http://www.hungry.com/~pere/ | Go Mozilla, go! Go! From redax@agria.hu Fri Jun 19 06:13:11 1998 Received: from lupus.agria.hu (root@agria.hu [194.196.126.2]) by hub.org (8.8.8/8.7.5) with ESMTP id CAA29041 for ; Fri, 19 Jun 1998 02:13:11 -0400 (EDT) Received: from lupus.agria.hu (redax@lupus.agria.hu [194.196.126.2]) by lupus.agria.hu (8.8.5/8.7.3) with SMTP id IAA31400; Fri, 19 Jun 1998 08:10:23 +0200 Date: Fri, 19 Jun 1998 08:10:23 +0200 (CEST) From: Zsolt Varga To: Petter Reinholdtsen cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] 'now - 4 hours ago' ? In-Reply-To: <199806181726.TAA25534@sleeper.games.no> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII On Thu, 18 Jun 1998, Petter Reinholdtsen wrote: | CREATE TABLE testdate( | received timestamp DEFAULT CURRENT_DATE, | info text NOT NULL | ); I think CURRENT_DATE will be parsed to the table creation's time. so better to use: CREATE TABLE testdate ( received timestamp DEFAULT timestamp(now()), info text NOT NULL ); Redax .----------------------------------------------------------. |Zsolt Varga | tel/fax: +36 36 422811 | | AgriaComputer LTD | email: redax@agria.hu | | System Administrator | URL: http://www.agria.hu/ | `----------------------------------------------------------' From djackson@cpsgroup.com Fri Jun 19 17:21:50 1998 Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.8.8/8.7.5) with ESMTP id NAA05334 for ; Fri, 19 Jun 1998 13:21:50 -0400 (EDT) Received: by bjke.com; id NAA06672; Fri, 19 Jun 1998 13:21:48 -0400 (EDT) Received: from cpsmail.cpsgroup.com(144.210.12.11) by gauntlet.bjke.com via smap (3.2) id xmab05978; Fri, 19 Jun 98 13:21:11 -0400 Received: by cpsmail with Internet Mail Service (5.0.1458.49) id ; Fri, 19 Jun 1998 12:22:03 -0500 Message-ID: From: "Jackson, DeJuan" To: Zsolt Varga , Petter Reinholdtsen Cc: pgsql-sql@postgreSQL.org Subject: RE: [SQL] 'now - 4 hours ago' ? Date: Fri, 19 Jun 1998 12:22:01 -0500 X-Priority: 3 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.0.1458.49) Content-Type: text/plain > On Thu, 18 Jun 1998, Petter Reinholdtsen wrote: > > | CREATE TABLE testdate( > | received timestamp DEFAULT CURRENT_DATE, > | info text NOT NULL > | ); > > I think CURRENT_DATE will be parsed to the table creation's time. > so better to use: > > CREATE TABLE testdate ( > received timestamp DEFAULT timestamp(now()), > info text NOT NULL > ); > > Redax > DATETIME has more features and a wider range than TIMESTAMP, so if I were you that's what I'd be using. try: SELECT NOW()::DATETIME - '@4 hours'::TIMESPAN; I suggest sticking with DATETIME as much as you can. -DEJ