pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (3+ messages)

Message-ID: <15044.41759.587859.18550@gargle.gargle.HOWL>
Permalink:  ../15044.41759.587859.18550@gargle.gargle.HOWL/
Also on:    postgresql.org/message-id/15044.41759.587859.18550@gargle.gargle.HOWL

 · 

reply

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