Forum OpenACS Development: current_timestamp = ('now'::text)::timestamp(6) with time zone

Collapse
1: current_timestamp = ('now'::text)::timestamp(6) with time zone
Posted by Tom Jackson on 07/18/03 05:48 PM

I'm looking at how postgres stores default values for table definitions, and for acs_objects, and probably a lot of other places, the default of now() has been replaced with current_timestamp in the table def. But when you look in the pg_ tables, this is stored as ('now'::text)::timestamp(6) with time zone. Selecting any of these three from psql gives the same result. So is current_timestamp a keyword like table, create, etc? Why was the change made from now() to current_timestamp?

Collapse
2: Re: current_timestamp = ('now'::text)::timestamp(6) with time zone (response to 1)
Posted by Roberto Mello on 07/18/03 05:52 PM
Because current_timestamp is part of the SQL standard and now() is PostgreSQL-specific?

-Roberto

Collapse
3: Re: current_timestamp = ('now'::text)::timestamp(6) with time zone (response to 1)
Posted by Ola Hansson on 07/18/03 06:05 PM
According to this SQL92 (SQL-2) Syntax Reference, current_timestamp is a reserved word like table, create and so on.

http://www.idera.com/support/documentation/SQL92__SQL_2__Syntax_Reference.htm

Collapse
4: Re: current_timestamp = ('now'::text)::timestamp(6) with time zone (response to 1)
Posted by Jarkko Laine on 07/18/03 06:19 PM
Tom,

You might even be interested in this thread: https://openacs.org/forums/message-view?message_id=111063

Collapse
5: Re: current_timestamp = ('now'::text)::timestamp(6) with time zone (response to 1)
Posted by Don Baccus on 07/18/03 11:47 PM
It's part of the standard, yes, and also once upon a time "now()" in some contexts was only executed once and the value cached, not sure if that's true or not with recent versions of PostgreSQL.

Oracle 9i, at least, also has the timestamp stuff from SQL 92 and eventually I'm hopeful we can use the standard syntax for both RDBMSs (even if other differences mean we can't truly share table definition code, drat)