current_timestamp after queries

On Mon, Sep 30, 2002 at 11:44:17AM +0200, Guido Staub wrote:

[some current_timestamp stuff]

I think that the accuracy is not good enough because I've started two
BEGIN statements and some time is elapsing between them. Am I right?
Or does anybody know a better solution to store the elapsed time after
some queries without writing some code in C or JAVA?

Perhaps you're looking for timeofday()?

kleptog=# begin; select timeofday(); select timeofday(); commit;
BEGIN
timeofday
-------------------------------------
Mon Sep 30 19:54:41.559605 2002 EST
(1 row)

timeofday
-------------------------------------
Mon Sep 30 19:54:41.560018 2002 EST
(1 row)

COMMIT

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

CURRENT_TIMESTAMP returns the time of the transaction start, not the
statement start. We are currently discussing on hackers whether this is
correct or not. We don't currently allow you to access the statement
start time. Sorry.

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

Guido Staub wrote:

Hi all,
I'm trying the following:
BEGIN;
select current_timestamp into mytable;
.
some queries
.
insert current timestamp into mytable;
COMMIT;
When I call this with the \i <filename> command, all is working fine,
but the two current_timestamp entries are the same, there is no
difference between them but there should. So I've tried:
BEGIN;
select current_timestamp into mytable;
.
some queries
.
COMMIT;
BEGIN;
insert current_timestamp into mytable;
COMMIT;
and now the entries are different.
I think that the accuracy is not good enough because I've started two
BEGIN statements and some time is elapsing between them. Am I right?
Or does anybody know a better solution to store the elapsed time after
some queries without writing some code in C or JAVA?

Thanks in advance
Guido Staub

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Yes, timeofday() will work, but it can change during the statement, right?

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

Martijn van Oosterhout wrote:

On Mon, Sep 30, 2002 at 11:44:17AM +0200, Guido Staub wrote:

[some current_timestamp stuff]

I think that the accuracy is not good enough because I've started two
BEGIN statements and some time is elapsing between them. Am I right?
Or does anybody know a better solution to store the elapsed time after
some queries without writing some code in C or JAVA?

Perhaps you're looking for timeofday()?

kleptog=# begin; select timeofday(); select timeofday(); commit;
BEGIN
timeofday
-------------------------------------
Mon Sep 30 19:54:41.559605 2002 EST
(1 row)

timeofday
-------------------------------------
Mon Sep 30 19:54:41.560018 2002 EST
(1 row)

COMMIT

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

There are 10 kinds of people in the world, those that can do binary
arithmetic and those that can't.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Is this because of time stamp caching and/or transaction coherency
issues?

Greg

Greg Copeland wrote:
-- Start of PGP signed section.

Is this because of time stamp caching and/or transaction coherency
issues?

It is because we thought that is what the standard required; now we are
not sure.

Greg

On Mon, 2002-09-30 at 10:02, Bruce Momjian wrote:

CURRENT_TIMESTAMP returns the time of the transaction start, not the
statement start. We are currently discussing on hackers whether this is
correct or not. We don't currently allow you to access the statement
start time. Sorry.

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

Guido Staub wrote:

Hi all,
I'm trying the following:
BEGIN;
select current_timestamp into mytable;
.
some queries
.
insert current timestamp into mytable;
COMMIT;
When I call this with the \i <filename> command, all is working fine,
but the two current_timestamp entries are the same, there is no
difference between them but there should. So I've tried:
BEGIN;
select current_timestamp into mytable;
.
some queries
.
COMMIT;
BEGIN;
insert current_timestamp into mytable;
COMMIT;
and now the entries are different.
I think that the accuracy is not good enough because I've started two
BEGIN statements and some time is elapsing between them. Am I right?
Or does anybody know a better solution to store the elapsed time after
some queries without writing some code in C or JAVA?

Thanks in advance
Guido Staub

-- 
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 359-1001
+  If your life is a hard drive,     |  13 Roberts Road
+  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- End of PGP section, PGP failed!

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Yes, timeofday() will work, but it can change during the statement, right?

For what he was doing, it seemed perfectly acceptable.

This comes back to the point I've been making during the pghackers
discussion: start-of-transaction time has clear uses, and
exact-current-time has clear uses, but it's not nearly as obvious
why you'd need start-of-statement time in preference to either of
the others.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Yes, timeofday() will work, but it can change during the statement, right?

For what he was doing, it seemed perfectly acceptable.

This comes back to the point I've been making during the pghackers
discussion: start-of-transaction time has clear uses, and
exact-current-time has clear uses, but it's not nearly as obvious
why you'd need start-of-statement time in preference to either of
the others.

How about:

BEGIN;
LOCK tab; -- could block
INSERT INTO tab VALUES (..., CURRENT_TIMESTAMP);

If this is an order-entry application, you would want the statement
start time, not the transaction start time. However, if you were
inserting this into several tables, we would want transaction timestamp
so it is always the same.

Is someone running Oracle 9 that can test this? We need:

BEGIN;
SELECT CURRENT_TIMESTAMP;
-- wait 5 seconds
SELECT CURRENT_TIMESTAMP;

Are those two timestamps the same?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

timeofday() works better than current_timestamp, but I have to use a
CAST-Statement to make it possible to calculate the elapsed time.
What do you mean with change during the statement?
The only change I see after using timeofday() is that the calculated time for
doing the query the first time and a second time again differ a llittle bit,
although that query1 and query2 are equal in the transaction and that I've used
the same relation.
Guido Staub

Bruce Momjian schrieb:

Guido Staub wrote:

timeofday() works better than current_timestamp, but I have to use a
CAST-Statement to make it possible to calculate the elapsed time.
What do you mean with change during the statement?
The only change I see after using timeofday() is that the calculated time for
doing the query the first time and a second time again differ a llittle bit,
although that query1 and query2 are equal in the transaction and that I've used
the same relation.
Guido Staub

Here is an example of timeofday() changing during a query:

test=> CREATE TEMP TABLE xx AS select timeofday() UNION select
timeofday();
SELECT
test=> SELECT * FROM xx;
timeofday
--------------------------------------
Tue Oct 01 17:09:23.381304 2002 CEST
Tue Oct 01 17:09:23.381393 2002 CEST
(2 rows)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073