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