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


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
Mon, 30 Sep 2002 11:44:17 +0200
Guido Staub <staub@gik.uni-karlsruhe.de>
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/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Mon, 30 Sep 2002 19:55:26 +1000
Martijn van Oosterhout <kleptog@svana.org>
Re: current_timestamp after queries

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

Mon, 30 Sep 2002 11:02:17 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: current_timestamp after queries

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

Mon, 30 Sep 2002 11:02:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: current_timestamp after queries
Is this because of time stamp caching and/or transaction coherency
issues?

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
30 Sep 2002 10:10:09 -0500
Greg Copeland <greg@CopelandConsulting.Net>
Re: [HACKERS] current_timestamp after queries
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

Mon, 30 Sep 2002 11:17:09 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] current_timestamp after queries
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

Mon, 30 Sep 2002 11:29:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: current_timestamp after queries 
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

Mon, 30 Sep 2002 11:34:57 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Need Oracle 9 tester.  was Re: current_timestamp after queries
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:

> 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


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:
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
Tue, 01 Oct 2002 12:25:02 +0200
Guido Staub <staub@gik.uni-karlsruhe.de>
Re: current_timestamp after queries
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

Tue, 1 Oct 2002 11:13:31 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: current_timestamp after queries