RE: Help with 'now', now(), timestamp 'now', ... - Mailing list pgsql-sql

<p><font size="2">try timeofday(), now() gives starttime of transaction :-{.</font><p><font
size="2">Regards</font><p><fontsize="2">Ben</font><p><font size="2">PS this has just been discussed in this mailing
listor I would have been clueless</font><p><font size="2">> -----Original Message-----</font><br /><font
size="2">>From: edipoelder@ig.com.br [<a
href="mailto:edipoelder@ig.com.br">mailto:edipoelder@ig.com.br</a>]</font><br/><font size="2">> Sent: 27 March 2001
19:02</font><br/><font size="2">> To: pgsql-sql@postgresql.org</font><br /><font size="2">> Subject: [SQL] Help
with'now', now(), timestamp 'now', ...</font><br /><font size="2">> </font><br /><font size="2">> </font><br
/><fontsize="2">> Hi all, </font><br /><font size="2">>  </font><br /><font size="2">> </font><br /><font
size="2">> </font><br /><font size="2">>     I´m developing some procedures in my db and i want know </font><br
/><fontsize="2">> how much time my </font><br /><font size="2">> </font><br /><font size="2">>  </font><br
/><fontsize="2">> procedures take to execute. So, i write my first procedure </font><br /><font size="2">> (to
test)as </font><br /><font size="2">>  </font><br /><font size="2">> follows: </font><br /><font size="2">> 
</font><br/><font size="2">> </font><br /><font size="2">>  </font><br /><font size="2">> CREATE FUNCTION
TEST(INTEGER)RETURNS INTEGER AS ' </font><br /><font size="2">>  </font><br /><font size="2">> DECLARE </font><br
/><fontsize="2">>  </font><br /><font size="2">>         SEQ RECORD; </font><br /><font size="2">>  </font><br
/><fontsize="2">> BEGIN </font><br /><font size="2">>  </font><br /><font size="2">>         SELECT
NEXTVAL(''TIMES_ID_SEQ'')AS ID INTO SEQ; </font><br /><font size="2">>  </font><br /><font size="2">>        
INSERTINTO TIMES (ID, START) VALUES (SEQ.ID, NOW()); </font><br /><font size="2">>  </font><br /><font
size="2">>        FOR I IN 1..$1 LOOP                           ^^^^^ </font><br /><font size="2">>  </font><br
/><fontsize="2">>                 INSERT INTO TEST(ANUMBER) VALUES (RANDOM()*$1); </font><br /><font size="2">> 
</font><br/><font size="2">>         END LOOP; </font><br /><font size="2">>  </font><br /><font
size="2">>        UPDATE TIMES SET END = NOW() WHERE ID = SEQ.ID; </font><br /><font size="2">>  </font><br
/><fontsize="2">>         RETURN SEQ.ID;         ^^^^^ </font><br /><font size="2">>  </font><br /><font
size="2">>END;' </font><br /><font size="2">>  </font><br /><font size="2">> LANGUAGE 'PLPGSQL'; </font><br
/><fontsize="2">>  </font><br /><font size="2">> </font><br /><font size="2">>  </font><br /><font
size="2">>    and executed using "SELECT TEST(10000);". When it finish, </font><br /><font size="2">> i do a
"SELECT</font><br /><font size="2">> </font><br /><font size="2">>  </font><br /><font size="2">> * FROM
TIMES"and got: </font><br /><font size="2">>  </font><br /><font size="2">> </font><br /><font size="2">> 
</font><br/><font size="2">> test=> SELECT * FROM TIMES; </font><br /><font size="2">>  </font><br /><font
size="2">>   start  |   end    | id </font><br /><font size="2">>  </font><br /><font size="2">>
----------+----------+----</font><br /><font size="2">>  </font><br /><font size="2">>  10:27:55 | 10:27:55 | 12
</font><br/><font size="2">>  </font><br /><font size="2">>  10:27:55 | 10:27:55 | 13 </font><br /><font
size="2">> </font><br /><font size="2">>  10:30:29 | 10:30:29 | 14 </font><br /><font size="2">>  </font><br
/><fontsize="2">>  10:31:29 | 10:31:29 | 15 </font><br /><font size="2">>  </font><br /><font size="2">> (4
rows)</font><br /><font size="2">>  </font><br /><font size="2">> </font><br /><font size="2">>  </font><br
/><fontsize="2">>     In id = 12 and id = 13, i runned two times. Then I </font><br /><font size="2">> changed
thefunction </font><br /><font size="2">> </font><br /><font size="2">>  </font><br /><font size="2">> and
run,at id = 14. Change again at id = 15. </font><br /><font size="2">>  </font><br /><font size="2">> </font><br
/><fontsize="2">>  </font><br /><font size="2">>     Where is underlined (^^^^), i tried to put, 'now',
</font><br/><font size="2">> timestamp 'now', etc, </font><br /><font size="2">> </font><br /><font
size="2">> </font><br /><font size="2">> and always get the same time. What i'm doing wrong? </font><br /><font
size="2">> </font><br /><font size="2">> </font><br /><font size="2">>  </font><br /><font size="2">>
obs.: -> TABLE TEST (ID SERIAL, ANUMBER INTEGER) </font><br /><font size="2">>  </font><br /><font
size="2">>       -> TABLE TIMES (ID SERIAL, START TIME, END TIME); </font><br /><font size="2">>  </font><br
/><fontsize="2">>        -> PostgreSQL 7.0.2 under Conectiva Linux</font><br /><font size="2">>  </font><br
/><fontsize="2">> </font><br /><font size="2">>  </font><br /><font size="2">> </font><br /><font
size="2">> </font><br /><font size="2">>     Thanks, </font><br /><font size="2">>  </font><br /><font
size="2">></font><br /><font size="2">>  </font><br /><font size="2">>     Edipo Elder</font><br /><font
size="2">> </font><br /><font size="2">>     [edipoelder@ig.com.br] </font><br /><font size="2">> </font><br
/><fontsize="2">> _________________________________________________________</font><br /><font size="2">> Oi! Você
querum iG-mail gratuito?</font><br /><font size="2">> Então clique aqui: <a
href="http://www.ig.com.br/paginas/assineigmail.html"
target="_blank">http://www.ig.com.br/paginas/assineigmail.html</a></font><br/><font size="2">> </font><br /><font
size="2">></font><br /><font size="2">> ---------------------------(end of </font><br /><font size="2">>
broadcast)---------------------------</font><br/><font size="2">> TIP 5: Have you checked our extensive
FAQ?</font><br/><font size="2">> </font><br /><font size="2">> <a
href="http://www.postgresql.org/users-lounge/docs/faq.html"
target="_blank">http://www.postgresql.org/users-lounge/docs/faq.html</a></font><br/><font size="2">>
</font><code><fontsize="3"><br /><br />
*****************************************************************************<br/> This email and any attachments
transmittedwith it are confidential<br /> and intended solely for the use of the individual or entity to whom<br />
theyare addressed. If you have received this email in error please<br /> notify the sender and do not store, copy or
disclosethe content<br /> to any other person.<br /><br /> It is the responsibility of the recipient to ensure that
openingthis<br /> message and/or any of its attachments will not adversely affect<br /> its systems. No responsibility
isaccepted by the Company.<br /> *****************************************************************************<br
/></font></code>