pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help with 'now', now(), timestamp 'now', ...
3+ messages / 3 participants
[nested] [flat]

* Help with 'now', now(), timestamp 'now', ...
@ 2001-03-27 18:01  edipoelder@ig.com.br
  0 siblings, 1 reply; 3+ messages in thread

From: edipoelder@ig.com.br @ 2001-03-27 18:01 UTC (permalink / raw)
  To: pgsql-sql

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); 
 
       -> PostgreSQL 7.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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: Help with 'now', now(), timestamp 'now', ...
@ 2001-03-30 14:49  Trewern, Ben <Ben.Trewern@mowlem.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Trewern, Ben @ 2001-03-30 14:49 UTC (permalink / raw)
  To: 'edipoelder@ig.com.br' <edipoelder@ig.com.br>; pgsql-sql

try timeofday(), now() gives starttime of transaction :-{.

Regards

Ben

PS this has just been discussed in this mailing list or I would have been
clueless

> -----Original Message-----
> From: edipoelder@ig.com.br [mailto:edipoelder@ig.com.br]
> Sent: 27 March 2001 19:02
> To: pgsql-sql@postgresql.org
> Subject: [SQL] 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); 
>  
>        -> PostgreSQL 7.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
> 
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


*****************************************************************************
This email and any attachments transmitted with it are confidential
and intended solely for the use of the individual or entity to whom
they are addressed. If you have received this email in error please
notify the sender and do not store, copy or disclose the content
to any other person.

It is the responsibility of the recipient to ensure that opening this
message and/or any of its attachments will not adversely affect
its systems. No responsibility is accepted by the Company.
*****************************************************************************

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Help with 'now', now(), timestamp 'now', ...
@ 2001-03-30 15:15  Daniel Wickstrom <danw@rtp.ericsson.se>
  parent: edipoelder@ig.com.br
  0 siblings, 0 replies; 3+ messages in thread

From: Daniel Wickstrom @ 2001-03-30 15:15 UTC (permalink / raw)
  To: edipoelder@ig.com.br; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-03-30 15:15 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-27 18:01 Help with 'now', now(), timestamp 'now', ... edipoelder@ig.com.br
2001-03-30 15:15 ` Daniel Wickstrom <danw@rtp.ericsson.se>
2001-03-30 14:49 RE: Help with 'now', now(), timestamp 'now', ... Trewern, Ben <Ben.Trewern@mowlem.com>

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