Postgres Pro
Downloads
Home   >   mailing lists

Thread: now() in loop statement

now() in loop statement

From
Kerv
Date:
Hello,

What is wrong with this function because the mytime variable contain the 
same value in each iteration:
CREATE OR REPLACE FUNCTION public.time_test()  RETURNS int4 AS
'
DECLAREcount int4;mytime timestamp;
BEGINcount := 0;mytime := now();while count <= 25 loop    mytime := now();    RAISE NOTICE \'Time: %\',mytime;    count
:=count + 1;end loop;RAISE NOTICE \'Count: %\',count;RETURN count;
 
END;
'  LANGUAGE 'plpgsql' VOLATILE;

the result is:
NOTICE:  Time: 2003-09-29 15:52:06.745317
.
.
.
NOTICE:  Time: 2003-09-29 15:52:06.745317 --same with the first value!!!
NOTICE:  Count: 26

Thanks.

Re: now() in loop statement

From
Tomasz Myrta
Date:
> Hello,
> 
> What is wrong with this function because the mytime variable contain the 
> same value in each iteration:

Just like it should be.
now() gives you always the same value inside single transaction. If you 
need time for performance tests - use "timeofday".

Regards,
Tomasz Myrta

Re: now() in loop statement

From
Christoph Haller
Date:
> 
> Hello,
> 
> What is wrong with this function because the mytime variable contain the 
> same value in each iteration:
> CREATE OR REPLACE FUNCTION public.time_test()
>    RETURNS int4 AS
> '
> DECLARE
>     count int4;
>     mytime timestamp;
> BEGIN
>     count := 0;
>     mytime := now();
>     while count <= 25 loop
>         mytime := now();
>         RAISE NOTICE \'Time: %\',mytime;
>         count := count + 1;
>     end loop;
>     RAISE NOTICE \'Count: %\',count;
>     RETURN count;
> END;
> '
>    LANGUAGE 'plpgsql' VOLATILE;
> 
> the result is:
> NOTICE:  Time: 2003-09-29 15:52:06.745317
> .
> .
> .
> NOTICE:  Time: 2003-09-29 15:52:06.745317 --same with the first value!!!
> NOTICE:  Count: 26
> 
> Thanks.
> 
Change 
mytime := now();
to 
mytime := ''now'';
and RTFM for details. 

Regards, Christoph

Re: now() in loop statement

From
"Dan Langille"
Date:
On 29 Sep 2003 at 15:58, Kerv wrote:

> What is wrong with this function because the mytime variable contain the 
> same value in each iteration:

From http://www.postgresql.org/docs/7.3/static/functions-
datetime.html:

It is important to realize that CURRENT_TIMESTAMP and related 
functions return the start time of the current transaction; their 
values do not change during the transaction. timeofday() returns the 
wall clock time and does advance during transactions. 
-- 
Dan Langille : http://www.langille.org/

Thanks for all replies

From
Kerv
Date:
Thank you,
Kerv