How do I make a timestamp column default to current time

"zagman" <daveh@allheller.net>

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

I think now() or current_timestamp both work.
But I cann't test it now:

create table foo (fno int, ftimestamp timestamp default now());
insert into foo(fno) values (1);
insert into foo(fno) values (2);
select * from foo;

Regards,
William ZHANG

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