From: Daniel Wickstrom <danw@rtp.ericsson.se>
To: edipoelder@ig.com.br
Cc: pgsql-sql@postgresql.org
Subject: Help with 'now', now(), timestamp 'now', ...
Date: Fri, 30 Mar 2001 10:15:43 -0500 (EST)
Message-ID: <15044.41759.587859.18550@gargle.gargle.HOWL> (raw)
In-Reply-To: <200103271801.f2RI1qw59361@mail.postgresql.org>
References: <200103271801.f2RI1qw59361@mail.postgresql.org>
>>>>> "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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: danw@rtp.ericsson.se, edipoelder@ig.com.br
Subject: Re: Help with 'now', now(), timestamp 'now', ...
In-Reply-To: <15044.41759.587859.18550@gargle.gargle.HOWL>
References: <200103271801.f2RI1qw59361@mail.postgresql.org> <15044.41759.587859.18550@gargle.gargle.HOWL>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox