Postgres Pro
Downloads
Home   >   mailing lists

Thread: How do I make a timestamp column default to current time

How do I make a timestamp column default to current time

From
"zagman"
Date:
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

Re: How do I make a timestamp column default to current time

From
Tom Lane
Date:
"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

Re: How do I make a timestamp column default to current time

From
"John D. Burger"
Date:
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

Re: How do I make a timestamp column default to current time

From
Tom Lane
Date:
"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

Re: How do I make a timestamp column default to current time

From
Bruce Momjian
Date:
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. +