Thread: PGsql function timestamp issue

PGsql function timestamp issue

From
ng
Date:

create or replace function dw.fx_nish()
returns text
  language plpgsql
  as
$$
declare
x timestamp with time zone;
y timestamp with time zone;
begin
x:= current_timestamp;
perform pg_sleep(5);
y:= current_timestamp;
if x=y then
return 'SAME';
else
return 'DIFFERENT';
end if;

end;
$$


select dw.fx_nish()
This give me 'SAME'

Any work around for this?

Re: PGsql function timestamp issue

From
"Jonathan S. Katz"
Date:
On May 29, 2014, at 4:46 PM, ng <pipelines@gmail.com> wrote:

create or replace function dw.fx_nish()
returns text
  language plpgsql
  as
$$
declare
x timestamp with time zone;
y timestamp with time zone;
begin
x:= current_timestamp;
perform pg_sleep(5);
y:= current_timestamp;
if x=y then
return 'SAME';
else
return 'DIFFERENT';
end if;

end;
$$


select dw.fx_nish()
This give me 'SAME'

Any work around for this?

Check out the Current Date/Time section:


You probably want "clock_timestamp()" depending on what you are trying to accomplish.

Jonathan

Re: PGsql function timestamp issue

From
Steve Crawford
Date:
On 05/29/2014 01:46 PM, ng wrote:
>
> create or replace function dw.fx_nish()
> returns text
>   language plpgsql
>   as
> $$
> declare
> x timestamp with time zone;
> y timestamp with time zone;
> begin
> x:= current_timestamp;
> perform pg_sleep(5);
> y:= current_timestamp;
> if x=y then
> return 'SAME';
> else
> return 'DIFFERENT';
> end if;
>
> end;
> $$
>
>
> select dw.fx_nish()
> This give me 'SAME'
>
> Any work around for this?

No and yes.

The value of current_timestamp will remain constant throughout a 
transaction so the function is returning the expected result.

You can use timeofday() but since that returns a string representing 
wall-clock time and does increment within a transaction. To get a 
timestamptz you will need to cast it: timeofday()::timestamptz

Cheers,
Steve




Re: PGsql function timestamp issue

From
bricklen
Date:
<div dir="ltr"><div class="gmail_extra"><br /><div class="gmail_quote">On Thu, May 29, 2014 at 2:13 PM, Steve Crawford
<spandir="ltr"><<a href="mailto:scrawford@pinpointresearch.com"
target="_blank">scrawford@pinpointresearch.com</a>></span>wrote:<br /><blockquote class="gmail_quote"
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On 05/29/2014 01:46 PM, ng wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br /> create or replace
functiondw.fx_nish()<br /> returns text<br />   language plpgsql<br />   as<br /> $$<br /> declare<br /> x timestamp
withtime zone;<br /> y timestamp with time zone;<br /> begin<br /> x:= current_timestamp;<br /> perform pg_sleep(5);<br
/>y:= current_timestamp;<br /> if x=y then<br /> return 'SAME';<br /> else<br /> return 'DIFFERENT';<br /> end if;<br
/><br/> end;<br /> $$<br /><br /><br /> select dw.fx_nish()<br /> This give me 'SAME'<br /><br /> Any work around for
this?<br/></blockquote><br /> No and yes.<br /><br /> The value of current_timestamp will remain constant throughout a
transactionso the function is returning the expected result.<br /><br /> You can use timeofday() but since that returns
astring representing wall-clock time and does increment within a transaction. To get a timestamptz you will need to
castit: timeofday()::timestamptz<br /></blockquote></div><br /></div><div class="gmail_extra">Or use
clock_timestamp()<br/></div></div> 

Re: PGsql function timestamp issue

From
Vinayak Pokale
Date:
Hello,

The current_timestamp return the constant value in a transaction. So use
clock_timestamp().
Example:
create or replace function fx_nish()
returns text language plpgsql as
$$
declare
x timestamp with time zone;
y timestamp with time zone;
begin
x:= clock_timestamp();
perform pg_sleep(5);
y:= clock_timestamp();
if x=y then
return 'SAME';
else
return 'DIFFERENT';
end if;

end;
$$
;
postgres=# select fx_nish(); fx_nish  
-----------DIFFERENT
(1 row)




-----
Thanks and Regards,
Vinayak Pokale,
NTT DATA OSS Center Pune, India
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PGsql-function-timestamp-issue-tp5805486p5812825.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.