Getting current time inside a transaction

This has already been on the lists but i hope it will be useful to somebody who has not time to read all of them. If you are doing stuff inside a transaction you may have noticed that the the results of NOW(), CURRENT_TIMESTAMP etc. will always return the same value, eg.

test=# begin;
BEGIN
test=# select now();
              now              
-------------------------------
 2007-11-02 16:57:38.011621+02
(1 row)

test=# select now();
              now              
-------------------------------
 2007-11-02 16:57:38.011621+02
(1 row)

test=# commit;
COMMIT

The time returned is always the transaction start date (don’t remember exactly though if it was the time of the begin statement or first SQL statement). However this can be overcome, there is a function that for whatever reasons i don’t know/remember and also really don’t care acts differently – it’s called timeofday().

test=# begin;
BEGIN
test=# select timeofday()::timestamptz;
           timeofday           
-------------------------------
 2007-11-02 17:01:15.042648+02
(1 row)

test=# select timeofday()::timestamptz;
           timeofday           
-------------------------------
 2007-11-02 17:01:19.266846+02
(1 row)

test=# commit;
COMMIT

Thanks to Ze / Hannu for pointing this out to me (bow)