agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedPGsql function timestamp issue
5+ messages / 5 participants
[nested] [flat]
* PGsql function timestamp issue
@ 2014-05-29 20:46 ng <pipelines@gmail.com>
2014-05-29 20:52 ` Re: PGsql function timestamp issue Jonathan S. Katz <jonathan.katz@excoventures.com>
2014-05-29 21:13 ` Re: PGsql function timestamp issue Steve Crawford <scrawford@pinpointresearch.com>
2014-07-25 08:59 ` Re: PGsql function timestamp issue Vinayak Pokale <vinpokale@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: ng @ 2014-05-29 20:46 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGsql function timestamp issue
2014-05-29 20:46 PGsql function timestamp issue ng <pipelines@gmail.com>
@ 2014-05-29 20:52 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
2 siblings, 0 replies; 5+ messages in thread
From: Jonathan S. Katz @ 2014-05-29 20:52 UTC (permalink / raw)
To: ng <pipelines@gmail.com>; +Cc: pgsql-sql
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:
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
You probably want "clock_timestamp()" depending on what you are trying to accomplish.
Jonathan=
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGsql function timestamp issue
2014-05-29 20:46 PGsql function timestamp issue ng <pipelines@gmail.com>
@ 2014-05-29 21:13 ` Steve Crawford <scrawford@pinpointresearch.com>
2014-05-29 22:00 ` Re: PGsql function timestamp issue bricklen <bricklen@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Steve Crawford @ 2014-05-29 21:13 UTC (permalink / raw)
To: ng <pipelines@gmail.com>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGsql function timestamp issue
2014-05-29 20:46 PGsql function timestamp issue ng <pipelines@gmail.com>
2014-05-29 21:13 ` Re: PGsql function timestamp issue Steve Crawford <scrawford@pinpointresearch.com>
@ 2014-05-29 22:00 ` bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: bricklen @ 2014-05-29 22:00 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: ng <pipelines@gmail.com>; pgsql-sql
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()
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGsql function timestamp issue
2014-05-29 20:46 PGsql function timestamp issue ng <pipelines@gmail.com>
@ 2014-07-25 08:59 ` Vinayak Pokale <vinpokale@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Vinayak Pokale @ 2014-07-25 08:59 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-07-25 08:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-05-29 20:46 PGsql function timestamp issue ng <pipelines@gmail.com>
2014-05-29 20:52 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
2014-05-29 21:13 ` Steve Crawford <scrawford@pinpointresearch.com>
2014-05-29 22:00 ` bricklen <bricklen@gmail.com>
2014-07-25 08:59 ` Vinayak Pokale <vinpokale@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox