Обсуждение: timestamp/now in views
Sorry for being an idiot but I saw something similar to this in the docs but haven't been able to find it since. 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. I hacked around with various combos of quote marks and tried now() and so on, but to no avail. Can someone enlighten me? Cheers,Graeme
"Graeme Merrall" <gmerrall@email.com> 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
> > Sorry for being an idiot but I saw something similar to this in the docs but > haven't been able to find it since. > 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'); ... WHERE audit_datetime > timestamp('now'::text) + '@24hours'::interval That one should work. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #