Received: from xxx-bfhxx-ms001.mowlem.com (mail2.mowlem.com [212.137.27.84]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f2UF0tK57742 for ; Fri, 30 Mar 2001 10:00:55 -0500 (EST) (envelope-from Ben.Trewern@mowlem.com) Received: from xxx-bfhxx-ex002.mowlem.com (unverified) by xxx-bfhxx-ms001.mowlem.com (Content Technologies SMTPRS 4.2.1) with ESMTP id ; Sat, 31 Mar 2001 15:48:02 +0100 Received: by XXX_BFHXX_EX002 with Internet Mail Service (5.5.2650.21) id ; Fri, 30 Mar 2001 15:49:57 +0100 Message-ID: <996802F75C3CD411B424001083FA445B059562@CET_PONXX_FP001> From: "Trewern, Ben" To: "'edipoelder@ig.com.br'" , pgsql-sql@postgresql.org Subject: RE: Help with 'now', now(), timestamp 'now', ... Date: Fri, 30 Mar 2001 15:49:46 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: multipart/alternative ; boundary="----_=_NextPart_001_01C0B928.B03A42D4" X-Archive-Number: 200103/640 X-Sequence-Number: 2318 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C0B928.B03A42D4 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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', ... >=20 >=20 > Hi all,=20 >=20=20 >=20 >=20=20 > I=B4m developing some procedures in my db and i want know=20 > how much time my=20 >=20 >=20=20 > procedures take to execute. So, i write my first procedure=20 > (to test) as=20 >=20=20 > follows:=20 >=20=20 >=20 >=20=20 > CREATE FUNCTION TEST(INTEGER) RETURNS INTEGER AS '=20 >=20=20 > DECLARE=20 >=20=20 > SEQ RECORD;=20 >=20=20 > BEGIN=20 >=20=20 > SELECT NEXTVAL(''TIMES_ID_SEQ'') AS ID INTO SEQ;=20 >=20=20 > INSERT INTO TIMES (ID, START) VALUES (SEQ.ID, NOW());=20 >=20=20 > FOR I IN 1..$1 LOOP ^^^^^=20 >=20=20 > INSERT INTO TEST(ANUMBER) VALUES (RANDOM()*$1);=20 >=20=20 > END LOOP;=20 >=20=20 > UPDATE TIMES SET END =3D NOW() WHERE ID =3D SEQ.ID;=20 >=20=20 > RETURN SEQ.ID; ^^^^^=20 >=20=20 > END;'=20 >=20=20 > LANGUAGE 'PLPGSQL';=20 >=20=20 >=20 >=20=20 > and executed using "SELECT TEST(10000);". When it finish,=20 > i do a "SELECT=20 >=20 >=20=20 > * FROM TIMES" and got:=20 >=20=20 >=20 >=20=20 > test=3D> SELECT * FROM TIMES;=20 >=20=20 > start | end | id=20 >=20=20 > ----------+----------+----=20 >=20=20 > 10:27:55 | 10:27:55 | 12=20 >=20=20 > 10:27:55 | 10:27:55 | 13=20 >=20=20 > 10:30:29 | 10:30:29 | 14=20 >=20=20 > 10:31:29 | 10:31:29 | 15=20 >=20=20 > (4 rows)=20 >=20=20 >=20 >=20=20 > In id =3D 12 and id =3D 13, i runned two times. Then I=20 > changed the function=20 >=20 >=20=20 > and run, at id =3D 14. Change again at id =3D 15.=20 >=20=20 >=20 >=20=20 > Where is underlined (^^^^), i tried to put, 'now',=20 > timestamp 'now', etc,=20 >=20 >=20=20 > and always get the same time. What i'm doing wrong?=20 >=20=20 >=20 >=20=20 > obs.: -> TABLE TEST (ID SERIAL, ANUMBER INTEGER)=20 >=20=20 > -> TABLE TIMES (ID SERIAL, START TIME, END TIME);=20 >=20=20 > -> PostgreSQL 7.0.2 under Conectiva Linux >=20=20 >=20 >=20=20 >=20 >=20=20 > Thanks,=20 >=20=20 >=20 >=20=20 > Edipo Elder >=20=20 > [edipoelder@ig.com.br]=20 >=20 > _________________________________________________________ > Oi! Voc=EA quer um iG-mail gratuito? > Ent=E3o clique aqui: http://www.ig.com.br/paginas/assineigmail.html >=20 >=20 > ---------------------------(end of=20 > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? >=20 > http://www.postgresql.org/users-lounge/docs/faq.html >=20 ***************************************************************************= ** 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. ***************************************************************************= ** ------_=_NextPart_001_01C0B928.B03A42D4 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [SQL] Help with 'now', now(), timestamp 'now', ...

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 'n= ow', ...
>
>
> Hi all,
> 
>
> 
>     I=B4m developing some proce= dures in my db and i want know
> how much time my
>
> 
> procedures take to execute. So, i write my first pr= ocedure
> (to test) as
> 
> follows:
> 
>
> 
> CREATE FUNCTION TEST(INTEGER) RETURNS INTEGER AS ' =
> 
> DECLARE
> 
>         SEQ= RECORD;
> 
> BEGIN
> 
>         SEL= ECT NEXTVAL(''TIMES_ID_SEQ'') AS ID INTO SEQ;
> 
>         INS= ERT INTO TIMES (ID, START) VALUES (SEQ.ID, NOW());
> 
>         FOR= I IN 1..$1 LOOP          = ;            &n= bsp;    ^^^^^
> 
>        &nbs= p;        INSERT INTO TEST(ANUMBER) VALU= ES (RANDOM()*$1);
> 
>         END= LOOP;
> 
>         UPD= ATE TIMES SET END =3D NOW() WHERE ID =3D SEQ.ID;
> 
>         RET= URN SEQ.ID;         ^^^^^
> 
> END;'
> 
> LANGUAGE 'PLPGSQL';
> 
>
> 
>     and executed using "SE= LECT TEST(10000);". When it finish,
> i do a "SELECT
>
> 
> * FROM TIMES" and got:
> 
>
> 
> test=3D> SELECT * FROM TIMES;
> 
>    start  |   end&nbs= p;   | 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 =3D 12 and id =3D 13,= i runned two times. Then I
> changed the function
>
> 
> and run, at id =3D 14. Change again at id =3D 15. <= /FONT>
> 
>
> 
>     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 IN= TEGER)
> 
>        -> TAB= LE TIMES (ID SERIAL, START TIME, END TIME);
> 
>        -> Pos= tgreSQL 7.0.2 under Conectiva Linux
> 
>
> 
>
> 
>     Thanks,
> 
>
> 
>     Edipo Elder
> 
>     [edipoelder@ig.com.br]
>
> ___________________________________________________= ______
> Oi! Voc=EA quer um iG-mail gratuito?
> Ent=E3o 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.
***************************************************************************= **
------_=_NextPart_001_01C0B928.B03A42D4--