values from now() in the same transaction

Vladimir Zelinski wrote:

I don't believe that it's bug, probably it's a feature
of the postgreSql database.

Correct.

Is any way to insert a timestamp within the same
transaction that would have current system time (not
time of the beginning of the transaction)?

timeofday()

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Thank you very much.
It works.

Vladimir

--- Alvaro Herrera <alvherre@commandprompt.com> wrote:

zelvlad@yahoo.com (Vladimir Zelinski) writes:

I tried function now(),current_timestamp() but all of
them behave similar.

I don't believe that it's bug, probably it's a feature
of the postgreSql database.

Indeed, that is intentional. CURRENT_TIMESTAMP and NOW() return the
time at which the transaction began.

Is any way to insert a timestamp within the same transaction that
would have current system time (not time of the beginning of the
transaction)?

With other words, I would like to see different timestamps on first
and last timestamp.

timeofday() is what you are looking for.

Consider the following series of queries; they demonstrate how the
behaviours of now() and timeofday() differ fairly successfully...

oxrsorg=# begin;
BEGIN
oxrsorg=# select now();
now
-------------------------------
2007-02-16 23:23:23.094817+00
(1 row)

oxrsorg=# select timeofday();
timeofday
-------------------------------------
Fri Feb 16 23:23:31.481780 2007 UTC
(1 row)

oxrsorg=# select timeofday();
timeofday
-------------------------------------
Fri Feb 16 23:23:32.981137 2007 UTC
(1 row)

oxrsorg=# select timeofday();
timeofday
-------------------------------------
Fri Feb 16 23:23:33.988252 2007 UTC
(1 row)

oxrsorg=# select now();
now
-------------------------------
2007-02-16 23:23:23.094817+00
(1 row)

oxrsorg=# select timeofday();
timeofday
-------------------------------------
Fri Feb 16 23:23:38.643998 2007 UTC
(1 row)

oxrsorg=# select now();
now
-------------------------------
2007-02-16 23:23:23.094817+00
(1 row)

--
(reverse (concatenate 'string "ofni.secnanifxunil" "@" "enworbbc"))
http://linuxfinances.info/info/finances.html
Rules of the Evil Overlord #189. "I will never tell the hero "Yes I
was the one who did it, but you'll never be able to prove it to that
incompetent old fool." Chances are, that incompetent old fool is
standing behind the curtain." <http://www.eviloverlord.com/&gt;

The problem with gettimeofday() is that it returns a string, rather than
a timestamp. This was all clarified in 8.2:

Add clock_timestamp(), statement_timestamp(), and
transaction_timestamp() (Bruce)
clock_timestamp() is the current wall-clock time,
statement_timestamp() is the time the current statement arrived at
the server, and transaction_timestamp() is an alias for now().

---------------------------------------------------------------------------

Chris Browne wrote:

zelvlad@yahoo.com (Vladimir Zelinski) writes:

I tried function now(),current_timestamp() but all of
them behave similar.

I don't believe that it's bug, probably it's a feature
of the postgreSql database.

Indeed, that is intentional. CURRENT_TIMESTAMP and NOW() return the
time at which the transaction began.

Is any way to insert a timestamp within the same transaction that
would have current system time (not time of the beginning of the
transaction)?

With other words, I would like to see different timestamps on first
and last timestamp.

timeofday() is what you are looking for.

Consider the following series of queries; they demonstrate how the
behaviours of now() and timeofday() differ fairly successfully...

oxrsorg=# begin;
BEGIN
oxrsorg=# select now();
now
-------------------------------
2007-02-16 23:23:23.094817+00
(1 row)

oxrsorg=# select timeofday();
timeofday
-------------------------------------
Fri Feb 16 23:23:31.481780 2007 UTC
(1 row)

oxrsorg=# select timeofday();
timeofday
-------------------------------------
Fri Feb 16 23:23:32.981137 2007 UTC
(1 row)

oxrsorg=# select timeofday();
timeofday
-------------------------------------
Fri Feb 16 23:23:33.988252 2007 UTC
(1 row)

oxrsorg=# select now();
now
-------------------------------
2007-02-16 23:23:23.094817+00
(1 row)

oxrsorg=# select timeofday();
timeofday
-------------------------------------
Fri Feb 16 23:23:38.643998 2007 UTC
(1 row)

oxrsorg=# select now();
now
-------------------------------
2007-02-16 23:23:23.094817+00
(1 row)

--
(reverse (concatenate 'string "ofni.secnanifxunil" "@" "enworbbc"))
http://linuxfinances.info/info/finances.html
Rules of the Evil Overlord #189. "I will never tell the hero "Yes I
was the one who did it, but you'll never be able to prove it to that
incompetent old fool." Chances are, that incompetent old fool is
standing behind the curtain." <http://www.eviloverlord.com/&gt;

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/16/07 17:25, Chris Browne wrote:

zelvlad@yahoo.com (Vladimir Zelinski) writes:

I tried function now(),current_timestamp() but all of
them behave similar.

I don't believe that it's bug, probably it's a feature
of the postgreSql database.

Indeed, that is intentional. CURRENT_TIMESTAMP and NOW() return the

Ooooh, is that Standard Behavior?

Is there a standard definition for CURRENT_TIMESTAMP?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF1k91S9HxQb37XmcRAqFsAKCMI+xzFxig2XMDPcsWcRMfToOJ/QCffWwO
iLBhZIc3jGp2VWwVSxW7hRQ=
=RTIl
-----END PGP SIGNATURE-----