Received: from imr2.ericy.com ([12.34.240.68]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f2UFG0K60981 for ; Fri, 30 Mar 2001 10:16:00 -0500 (EST) (envelope-from danw@netmanager7.rtp.ericsson.se) Received: from mr3.exu.ericsson.se (mr3att.ericy.com [138.85.92.10]) by imr2.ericy.com (8.11.3/8.11.3) with ESMTP id f2UFFq802580 for ; Fri, 30 Mar 2001 09:15:56 -0600 (CST) Received: from eamrcnt749 (eamrcnt749.exu.ericsson.se [138.85.133.47]) by mr3.exu.ericsson.se (8.10.2/8.10.2) with SMTP id f2UFFqL29036 for ; Fri, 30 Mar 2001 09:15:52 -0600 (CST) Received: FROM netmanager7.rtp.ericsson.se BY eamrcnt749 ; Fri Mar 30 09:15:47 2001 -0600 Received: from satdsp14.ericsson.COM (satdsp14.rtp.ericsson.se [147.117.84.7]) by netmanager7.rtp.ericsson.se (8.8.8+Sun/8.6.4) with ESMTP id KAA12699; Fri, 30 Mar 2001 10:15:46 -0500 (EST) Received: (from danw@localhost) by satdsp14.ericsson.COM (8.9.1b+Sun/8.9.1) id KAA06189; Fri, 30 Mar 2001 10:15:45 -0500 (EST) From: Daniel Wickstrom MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Message-ID: <15044.41759.587859.18550@gargle.gargle.HOWL> Date: Fri, 30 Mar 2001 10:15:43 -0500 (EST) To: edipoelder@ig.com.br Cc: pgsql-sql@postgresql.org Subject: Help with 'now', now(), timestamp 'now', ... In-Reply-To: <200103271801.f2RI1qw59361@mail.postgresql.org> References: <200103271801.f2RI1qw59361@mail.postgresql.org> X-Mailer: VM 6.72 under 21.1 (patch 10) "Capitol Reef" XEmacs Lucid Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from base64 to 8bit by postgresql.org id f2UFG1K60982 X-Archive-Number: 200103/642 X-Sequence-Number: 2320 >>>>> "edipoelder" == edipoelder 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