Thread: current_timestamp after queries
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?
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
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.
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 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
Attachment
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
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
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