Thread: values from now() in the same transaction
I created a function with VOLATILE directive. it's body looks like shown bellow ------------------------ cut start begin insert into monitor(ts, c1) values(LOCALTIMESTAMP, 'Step 1000'); -- start time -- query below runs for 20min insert ito t1 select * from big_table -- this timestamp should be bigger by 20min than start time insert into monitor(ts, c1) values(LOCALTIMESTAMP, 'Step 1001'); end ----------------------- cut end The value returned by LOCALTIMESTAMP function is the same in both places despite that actual interval of 20 min between these calls. 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. 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. Thank you, Vladimir
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: > 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 > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ >
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/>
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/> > > ---------------------------(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-----