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

Tue, 13 Nov 2001 20:38:41 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Use of 'now'
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

Tue, 13 Nov 2001 21:27:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Use of 'now' 
> 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

Tue, 13 Nov 2001 21:46:32 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Use of 'now'
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

Tue, 13 Nov 2001 21:52:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Use of 'now' 
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
==========================================================================




Tue, 13 Nov 2001 22:15:22 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: Use of 'now'
> > 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

Tue, 13 Nov 2001 22:40:13 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Use of 'now'
> 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

Tue, 13 Nov 2001 22:58:48 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Use of 'now'
...
> 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

Wed, 14 Nov 2001 07:08:34 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Use of 'now'
> [ 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

Wed, 14 Nov 2001 07:23:30 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Use of 'now'
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
==========================================================================




Wed, 14 Nov 2001 05:59:46 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: Use of 'now'
...
> 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

Wed, 14 Nov 2001 14:11:05 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Use of 'now'
> 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

Wed, 14 Nov 2001 11:10:14 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Use of 'now'


Bruce Momjian wrote:

>>>I don't think this is a good idea.  If someone really relied on 'current'
>>>for his application, substituting 'now' for it is not going to make things
>>>better.  If it's done silently it will definitely make things worse.
>>>
>>I hadn't thought about it, but I believe Peter is right.  Rejecting
>>'current' is better than silently translating it to 'now'.  We have
>>removed this feature and we shouldn't try to paper over the fact.
>>
>
>My only question is how many people were using current thinking it
>functioned as 'now'?  Was current ever a desired feature?
>
The only times I have used current were by mistake when PG interpreted 
something
starting with current as current. I suspect that everybody who has been 
using current
thinking it functions as 'now' has either found out it does not or does 
not really care ;)
-----------
Hannu




Thu, 15 Nov 2001 19:54:44 +0500
Hannu Krosing <hannu@sid.tm.ee>
Re: [DOCS] Use of 'now'
Thomas Lockhart writes:

> 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.

I don't think this is a good idea.  If someone really relied on 'current'
for his application, substituting 'now' for it is not going to make things
better.  If it's done silently it will definitely make things worse.

If that someone replays his dump and sees "invalid date/time value
'current'" then he knows he's got something to fix -- and he has to fix
something anyway.

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 15 Nov 2001 17:15:48 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [DOCS] Use of 'now'
Peter Eisentraut <peter_e@gmx.net> writes:
> Thomas Lockhart writes:
>> 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.

> I don't think this is a good idea.  If someone really relied on 'current'
> for his application, substituting 'now' for it is not going to make things
> better.  If it's done silently it will definitely make things worse.

I hadn't thought about it, but I believe Peter is right.  Rejecting
'current' is better than silently translating it to 'now'.  We have
removed this feature and we shouldn't try to paper over the fact.

			regards, tom lane

Thu, 15 Nov 2001 11:36:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [DOCS] Use of 'now' 
> > I don't think this is a good idea.  If someone really relied on 'current'
> > for his application, substituting 'now' for it is not going to make things
> > better.  If it's done silently it will definitely make things worse.
> 
> I hadn't thought about it, but I believe Peter is right.  Rejecting
> 'current' is better than silently translating it to 'now'.  We have
> removed this feature and we shouldn't try to paper over the fact.

My only question is how many people were using current thinking it
functioned as 'now'?  Was current ever a desired feature?

-- 
  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

Thu, 15 Nov 2001 11:39:16 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [DOCS] Use of 'now'