Re: CURRENT_TIMESTAMP vs actual time

On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote:

> I understand that CURRENT_TIMESTAMP marks the beginning of the current
> transaction. I want it to be the actual time. How do I do this?
> timeofday() returns a string, how do I convert that into a TIMESTAMP?

timeofday()::timestamp;

> Is it possible to create a column with DEFAULT value evaluated to the
> actual
> current time (i.e. not the CURRENT_TIMESTAMP which is the beginning of
> the
> current transaction).

Sure. Use the expression above or create a function for it:

create or replace function timeofday_stamp() returns timestamp as
'select timeofday()::timestamp;'
language sql volatile;

create table test_stamp (
id integer primary key,
my_stamp timestamp default timeofday_stamp()
);

insert into test_stamp values (1);
insert into test_stamp values (2);

select * from test_stamp;
id | my_stamp
----+----------------------------
1 | 2005-04-20 19:35:59.884837
2 | 2005-04-20 19:36:13.719402
(2 rows)

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL