pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp 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
2001-03-30 15:15 ` Help with 'now', now(), timestamp 'now', ... Daniel Wickstrom <danw@rtp.ericsson.se>
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
* Help with 'now', now(), timestamp 'now', ...
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>
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
* 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
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