Обсуждение: How do I make a timestamp column default to current time

Hi,

I'm setting up a simple timecard program and I need a column that
defaults to the current time when a new row is inserted. How can I do
this I tried setting the default to 'now' but all that does is put the
time I created the table in each row! I'm also new to postgresql and if
someone can give me a pointer or two in the right direction I would be
appreciative.

Thanks,

Dave

"zagman" <daveh@allheller.net> writes:
> I'm setting up a simple timecard program and I need a column that
> defaults to the current time when a new row is inserted. How can I do
> this I tried setting the default to 'now' but all that does is put the
> time I created the table in each row!

The default has to be a function call, not a literal constant, to work
the way you want.  Try

    mycol timestamp default now()
or
    mycol timestamp default current_timestamp

(the latter is actually a function call, even though the SQL standard
says it has to be spelled without any parentheses)

            regards, tom lane

Tom Lane writes:

> Try
>
>     mycol timestamp default now()
> or
>     mycol timestamp default current_timestamp
>
> (the latter is actually a function call, even though the SQL standard
> says it has to be spelled without any parentheses)

And both of these return start time of the current transaction, yes?
Is it the case that there is no SQL-standard way to get the current
time?  I know Postgresql has timeofday(), but it's not standard (plus
it's weird that it returns a text string).

- John D. Burger
   MITRE

"John D. Burger" <john@mitre.org> writes:
>> mycol timestamp default now()
>> or
>> mycol timestamp default current_timestamp

> And both of these return start time of the current transaction, yes?
> Is it the case that there is no SQL-standard way to get the current
> time?

AFAIK the spec doesn't really define the meaning of current_timestamp
all that closely.  I believe there's a patch in the queue to add some
additional nonstandard functions for current time and start of current
interactive command, though.

            regards, tom lane

Tom Lane wrote:
> "John D. Burger" <john@mitre.org> writes:
> >> mycol timestamp default now()
> >> or
> >> mycol timestamp default current_timestamp
>
> > And both of these return start time of the current transaction, yes?
> > Is it the case that there is no SQL-standard way to get the current
> > time?
>
> AFAIK the spec doesn't really define the meaning of current_timestamp
> all that closely.  I believe there's a patch in the queue to add some
> additional nonstandard functions for current time and start of current
> interactive command, though.

I am working on a patch for 8.2 for this:

    * Add transaction_timestamp(), statement_timestamp(), clock_timestamp()
      functionality

      Current CURRENT_TIMESTAMP returns the start time of the current
      transaction, and gettimeofday() returns the wallclock time. This will
      make time reporting more consistent and will allow reporting of
      the statement start time.

--
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +