Lists: | pgsql-bugs |
---|
From: | "" <wcting163(at)163(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #6047: prepare p1 as select 'now'::timestamp; then "execute p1" many times, they return the same time |
Date: | 2011-06-02 02:53:01 |
Message-ID: | 201106020253.p522r1eD076376@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 6047
Logged by:
Email address: wcting163(at)163(dot)com
PostgreSQL version: 9.0.4
Operating system: WinXP 32bit
Description: prepare p1 as select 'now'::timestamp; then "execute p1"
many times, they return the same time
Details:
when i execute the following statements:
prepare p1 as select 'now'::timestamp;
execute p1;
execute p1;
every time i execute statement "execute p1", they all return the same time,
the time when "prepare p1 ..." is created.
but i expect it return the changing time ---- the current time.
1). although i kown that, "the system will convert **now** to a timestamp as
soon as the constant is parsed",
i think this is a bug.
2). maybe you suggest me:
prepare p1 as select now()::timestamp;
then "execute p1" will return the current time.
this is because now() is a builtin function, so we can change "select
'now'::timestamp" to "select now()::timestamp";
but, what about "prepare p1 as select 'today'::timestamp"??
today() is not a builtin function, we can't change it to "select
today()::timestamp";
So, the implementation method of "now" and "today" is different, and
prepare p1 as select 'today'::timestamp;
execute p1;-- will always return the day when the "prepare p1 ..." is
created, even we're in another day.
From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | wcting163(at)163(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6047: prepare p1 as select 'now'::timestamp; then "execute p1" many times, they return the same time |
Date: | 2011-06-02 08:51:21 |
Message-ID: | 4DE74F09.8080404@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On 02/06/11 10:53, wcting163(at)163(dot)com wrote:
> but, what about "prepare p1 as select 'today'::timestamp"??
> today() is not a builtin function, we can't change it to "select
> today()::timestamp";
The keyword-to-timestamp conversions are ugly historical hacks.
Use the SQL-standard current_date, current_time, and current_timestamp
keywords instead.
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
--
Craig Ringer
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "" <wcting163(at)163(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6047: prepare p1 as select 'now'::timestamp; then "execute p1" many times, they return the same time |
Date: | 2011-06-02 14:10:04 |
Message-ID: | 14435.1307023804@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"" <wcting163(at)163(dot)com> writes:
> 1). although i kown that, "the system will convert **now** to a timestamp as
> soon as the constant is parsed",
> i think this is a bug.
Sorry, it's not a bug, and we're not going to change it.
> but, what about "prepare p1 as select 'today'::timestamp"??
> today() is not a builtin function, we can't change it to "select
> today()::timestamp";
You can get that by using date_trunc on the result of now(); or there's
current_date, which is actually SQL-standard unlike these other things.
regards, tom lane