Обсуждение: PGsql function timestamp issue
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?
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
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
<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>
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.