Thread: Help with 'now', now(), timestamp 'now', ...

Hi all, 
   I�m developing some procedures in my db and i want know how much time my 

procedures take to execute. So, i write my first procedure (to test) as 
follows: 

CREATE FUNCTION TEST(INTEGER) RETURNS INTEGER AS ' 
DECLARE        SEQ RECORD; 
BEGIN        SELECT NEXTVAL(''TIMES_ID_SEQ'') AS ID INTO SEQ;        INSERT INTO TIMES (ID, START) VALUES (SEQ.ID,
NOW());       FOR I IN 1..$1 LOOP                           ^^^^^                INSERT INTO TEST(ANUMBER) VALUES
(RANDOM()*$1);       END LOOP;        UPDATE TIMES SET END = NOW() WHERE ID = SEQ.ID;        RETURN SEQ.ID;
^^^^^
 
END;' 
LANGUAGE 'PLPGSQL'; 
   and executed using "SELECT TEST(10000);". When it finish, i do a "SELECT 

* FROM TIMES" and got: 

test=> SELECT * FROM TIMES;   start  |   end    | id 
----------+----------+---- 10:27:55 | 10:27:55 | 12 10:27:55 | 10:27:55 | 13 10:30:29 | 10:30:29 | 14 10:31:29 |
10:31:29| 15 
 
(4 rows) 
   In id = 12 and id = 13, i runned two times. Then I changed the function 

and run, at id = 14. Change again at id = 15. 
   Where is underlined (^^^^), i tried to put, 'now', timestamp 'now', etc, 

and always get the same time. What i'm doing wrong? 

obs.:  -> TABLE TEST (ID SERIAL, ANUMBER INTEGER)       -> TABLE TIMES (ID SERIAL, START TIME, END TIME);       ->
PostgreSQL7.0.2 under Conectiva Linux
 

   Thanks, 
   Edipo Elder   [edipoelder@ig.com.br] 

_________________________________________________________
Oi! Voc� quer um iG-mail gratuito?
Ent�o clique aqui: http://www.ig.com.br/paginas/assineigmail.html

<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>

>>>>> "edipoelder" == edipoelder  <edipoelder@ig.com.br> writes:
   edipoelder> times. Then I changed the function and run, at id =   edipoelder> 14. Change again at id = 15. Where is
underlined  edipoelder> (^^^^), i tried to put, 'now', timestamp 'now', etc,    edipoelder> and always get the same
time.What i'm doing wrong?    edipoelder> obs.: -> TABLE TEST (ID SERIAL, ANUMBER INTEGER)    -> TABLE TIMES (ID
SERIAL,START TIME, END TIME); PostgreSQL   -> 7.0.2 under Conectiva Linux
 

now() returns the time at the start of a transaction.  Since pl/psql
functions are implicit transactions, now() will not change inside a
function call.

I've used something like the following:

create function bm(integer) returns text as '
declare       cnt      alias for $1;       startt   text;       endt     text;
begin       startt := timeofday();       for i in 1..cnt LOOP        
           -- insert statement you want to time here
       end loop;       endt := timeofday();
       return delta_time_msecs(startt,endt);

end;' language 'plpgsql';

create function delta_time_msecs(text,text) returns float8 as '
declare       startt          alias for $1;       endt            alias for $2;       span            interval;
days           float8;       hours           float8;       minutes         float8;       mseconds        float8;
 
begin       span     := endt::timestamp - startt::timestamp;       mseconds :=
date_part(''milliseconds'',span)::float8;      minutes  := date_part(''minutes'',span)::float8;       hours    :=
date_part(''hours'',span)::float8;      days     := date_part(''days'',span)::float8;
 
       return abs(mseconds + minutes*60.0*1000.0 + hours*3600.0*1000.0 + days*24.0*3600.0*1000.0);

end;' language 'plpgsql';

select bm(1000)::float8/1000.0;

This will give you the average time, averaged over a thousand queries.  

-Dan