Обсуждение: 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
On Thu, May 29, 2014 at 2:13 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
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
Or use clock_timestamp()
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.