Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA16830 for ; Wed, 8 Dec 1999 20:16:31 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id UAA29106; Wed, 8 Dec 1999 20:15:56 -0500 (EST) To: "Graeme Merrall" cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] timestamp/now in views In-reply-to: Your message of Thu, 9 Dec 1999 10:18:47 +1100 Date: Wed, 08 Dec 1999 20:15:56 -0500 Message-ID: <29104.944702156@sss.pgh.pa.us> From: Tom Lane "Graeme Merrall" writes: > I tried creating a view using the following syntax > CREATE VIEW prev_day AS SELECT * FROM audit WHERE audit_datetime > > (current_timestamp - interval'24 hours'); > Now as some of you will pick up, this creates a view with the current > timestamp set to the creation time of the view, not the actual time the view > was executed. Yes --- this is fixed in current sources, but that doesn't help you on release versions :-( > I hacked around with various combos of quote marks and tried now() and so > on, but to no avail. Just substituting now() for current_timestamp seemed to work fine for me in 6.5.3: play=> CREATE VIEW prev_day AS SELECT * FROM audit WHERE audit_datetime > play-> (now() - interval'24 hours'); CREATE play=> \d prev_day View = prev_day Query = SELECT "audit"."f1", "audit"."audit_datetime" FROM "audit" WHERE ("datetime"("audit"."audit_datetime") > ("datetime"("now"()) - '@ 1 day'::"timespan")); As you can see, the stored form of the query still has now() rather than a constant datetime value... regards, tom lane