Thread: Use of 'now'
Tatsuo found the following paragraph in the docs, in datatype.sgml: --------------------------------------------------------------------------- <literal>'now'</literal> is resolved when the value is inserted, <literal>'current'</literal> is resolved every time the value is retrieved. So you probably want to use <literal>'now'</literal> in most applications. (Of course you <emphasis>really</emphasis> want to use <literal>CURRENT_TIMESTAMP</literal>, which is equivalent to <literal>'now'</literal>.) --------------------------------------------------------------------------- This seems wrong to me. What does it mean when it says 'current' is resolved every time the value is retrieved? Also, we mention 'now' a lot in the documentation. Should we change those to CURRENT_TIMESTAMP? I have change that in the FAQ. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> This seems wrong to me. What does it mean when it says 'current' is
> resolved every time the value is retrieved?
Nothing of interest anymore, since 'current' has been removed as of 7.2.
However, Thomas has yet to commit any docs updates for his recent
datetime-related changes ... including that one ...
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Nothing of interest anymore, since 'current' has been removed as of 7.2.
>> However, Thomas has yet to commit any docs updates for his recent
>> datetime-related changes ... including that one ...
> Seems it is still in there somewhere:
> test=> create table bb (x timestamp default 'current', y int);
Hmm. It was *supposed* to be removed entirely, but possibly what
Thomas actually did was to continue to accept the keyword as equivalent
to 'now'. Thomas?
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > This seems wrong to me. What does it mean when it says 'current' is
> > resolved every time the value is retrieved?
>
> Nothing of interest anymore, since 'current' has been removed as of 7.2.
> However, Thomas has yet to commit any docs updates for his recent
> datetime-related changes ... including that one ...
Seems it is still in there somewhere:
test=> create table bb (x timestamp default 'current', y int);
CREATE
test=> insert into bb (y) values (1);
INSERT 16591 1
test=> select * from bb;
x | y
-------------------------------+---
2001-11-13 21:45:22.473896-05 | 1
(1 row)
Do you mean that 'current' is now the same as 'now'? :-)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Tue, 13 Nov 2001, Bruce Momjian wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > This seems wrong to me. What does it mean when it says 'current' is > > > resolved every time the value is retrieved? > > > > Nothing of interest anymore, since 'current' has been removed as of 7.2. > > However, Thomas has yet to commit any docs updates for his recent > > datetime-related changes ... including that one ... > > Seems it is still in there somewhere: > > test=> create table bb (x timestamp default 'current', y int); > CREATE > test=> insert into bb (y) values (1); > INSERT 16591 1 > test=> select * from bb; > x | y > -------------------------------+--- > 2001-11-13 21:45:22.473896-05 | 1 > (1 row) > > Do you mean that 'current' is now the same as 'now'? :-) ISTM that 'current' when used as a default meant the time the table was created but now() would (as one woule expect) return the current datetime. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Nothing of interest anymore, since 'current' has been removed as of 7.2.
> >> However, Thomas has yet to commit any docs updates for his recent
> >> datetime-related changes ... including that one ...
>
> > Seems it is still in there somewhere:
>
> > test=> create table bb (x timestamp default 'current', y int);
>
> Hmm. It was *supposed* to be removed entirely, but possibly what
> Thomas actually did was to continue to accept the keyword as equivalent
> to 'now'. Thomas?
[ CC'ing to hackers because this is getting into code problems. ]
Here's another inconsistency that Tatsuo found:
test=> create table ff (x time default 'current_timestamp');
ERROR: Bad time external representation 'current_timestamp'
test=> create table ff (x time default 'current');
ERROR: Bad time external representation 'current'
test=> create table ff (x time default 'now');
CREATE
test=> select current_timestamp;
timestamptz
-------------------------------
2001-11-13 22:49:50.607401-05
(1 row)
You can default a time to now, but not to current or current_timestamp.
I believe this is happening because current is implemented as special
timezones in datetime.c and timestamp.c, and current_timestamp is
implemented in gram.y, while 'now' is a function.
Anyway, looks like confusion that should be fixed.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> > Seems it is still in there somewhere:
> >
> > test=> create table bb (x timestamp default 'current', y int);
> > CREATE
> > test=> insert into bb (y) values (1);
> > INSERT 16591 1
> > test=> select * from bb;
> > x | y
> > -------------------------------+---
> > 2001-11-13 21:45:22.473896-05 | 1
> > (1 row)
> >
> > Do you mean that 'current' is now the same as 'now'? :-)
>
> ISTM that 'current' when used as a default meant the time the table
> was created but now() would (as one woule expect) return the current
> datetime.
You would think so, but in fact 'current' does change for each insert:
test=> create table dd (x timestamp default 'current', y int);
CREATE
test=> insert into dd (y) values (1);
INSERT 16596 1
test=> insert into dd (y) values (1);
INSERT 16597 1
test=> select * from dd;
x | y
-------------------------------+---
2001-11-13 22:39:18.283834-05 | 1
2001-11-13 22:39:19.196797-05 | 1
(2 rows)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
...
> Hmm. It was *supposed* to be removed entirely, but possibly what
> Thomas actually did was to continue to accept the keyword as equivalent
> to 'now'. Thomas?
Not sure where "supposed to" came from ;)
Previous versions of PostgreSQL can and will generate dump files which
have 'current'. I did make it equivalent to 'now' for at least the 7.2
series of releases.
- Thomas
> [ CC'ing to hackers because this is getting into code problems. ]
Not sure I agree with that conclusion yet.
> Here's another inconsistency that Tatsuo found:
> test=> create table ff (x time default 'current_timestamp');
> ERROR: Bad time external representation 'current_timestamp'
Never was a feature, and not documented as such. CURRENT_TIMESTAMP (and
CURRENT_DATE and CURRENT_TIME; note lack of quotes) are defined by SQL9x
as specialty constants (they have some other term for them afaicr).
> test=> create table ff (x time default 'current');
> ERROR: Bad time external representation 'current'
Never was a feature, but sure seems like it should have been. How have
we missed all of those complaints about this over the last six years? ;)
We'll guess that 'current' was not one of the most utilized features of
the date/time types (which is one reason why I supported removing it).
> test=> create table ff (x time default 'now');
> CREATE
> test=> select current_timestamp;
> timestamptz
> -------------------------------
> 2001-11-13 22:49:50.607401-05
> (1 row)
>
> You can default a time to now, but not to current or current_timestamp.
>
> I believe this is happening because current is implemented as special
> timezones in datetime.c and timestamp.c, and current_timestamp is
> implemented in gram.y, while 'now' is a function.
Not sure what special time zones have to do with it (did you mean
"special timestamps"?). CURRENT_xxx has to be implemented in gram.y
since they are keywords, not quoted strings. 'now' is not a function,
though now() is; both 'now' and 'current' are special cases in the input
parser for the date/time data types, with one inconsistancy as noted
above. That will be fixed.
> Anyway, looks like confusion that should be fixed.
The documentation covers some of this, and Tom has pointed out
(presumably to encourage a contribution) that it hasn't been updated yet
for the most recent changes for 7.2. I expect to do so in the next
couple of weeks.
- Thomas
On Tue, 13 Nov 2001, Bruce Momjian wrote:
> > > Seems it is still in there somewhere:
> > >
> > > test=> create table bb (x timestamp default 'current', y int);
> > > CREATE
> > > test=> insert into bb (y) values (1);
> > > INSERT 16591 1
> > > test=> select * from bb;
> > > x | y
> > > -------------------------------+---
> > > 2001-11-13 21:45:22.473896-05 | 1
> > > (1 row)
> > >
> > > Do you mean that 'current' is now the same as 'now'? :-)
> >
> > ISTM that 'current' when used as a default meant the time the table
> > was created but now() would (as one woule expect) return the current
> > datetime.
>
> You would think so, but in fact 'current' does change for each insert:
>
> test=> create table dd (x timestamp default 'current', y int);
> CREATE
> test=> insert into dd (y) values (1);
> INSERT 16596 1
> test=> insert into dd (y) values (1);
> INSERT 16597 1
> test=> select * from dd;
> x | y
> -------------------------------+---
> 2001-11-13 22:39:18.283834-05 | 1
> 2001-11-13 22:39:19.196797-05 | 1
> (2 rows)
>
>
Or this:
PostgreSQL 7.0.3 on i386-unknown-freebsdelf4.2, compiled by gcc 2.95.2
(1 row)
template1=# create table dd (x timestamp default 'current', y int);
CREATE
template1=# insert into dd (y) values (1);
INSERT 1407083 1
template1=# insert into dd (y) values (1);
INSERT 1407084 1
template1=# select * from dd;
x | y
---------+---
current | 1
current | 1
(2 rows)
Must be since 7.0.3?
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
...
> template1=# insert into dd (y) values (1);
> template1=# select * from dd;
> x | y
> ---------+---
> current | 1
> Must be since 7.0.3?
Prior to 7.2 (and up to two months ago -- ?? haven't checked the dates)
'current' was stored as a special value. It was only evaluated as the
current transaction time when math or some other transformation was
involved.
The feature dates from sometime after 1987 and sometime before 1995
(back when gods roamed the earth, etc etc).
Regarding the TIME data type: there was never a reserved value defined
for that type, so the feature was never available for it. Since
'current' and 'now' are synonymous, it is a one-liner to add recognition
of 'current' to that type. I've got patches...
- Thomas
> Regarding the TIME data type: there was never a reserved value defined > for that type, so the feature was never available for it. Since > 'current' and 'now' are synonymous, it is a one-liner to add recognition > of 'current' to that type. I've got patches... You know I am totally lost with the date/time stuff. I am just pointing out stuff and guessing. Please do whatever you think is appropriate. Thanks. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026