Обсуждение: SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR
Hello,
--
Piotr Gasidło
I found strange PostgreSQL 9.3 behavior:
> select now()::timestamp, 'now()'::timestamp;
now | timestamp
----------------------------+----------------------------
2014-08-22 08:34:00.883268 | 2014-08-22 08:34:00.883268
Second column is now() in single apostrophes.
Now, I tried similar function, clock_timestamp() and get:
> select clock_timestamp()::timestamp, 'clock_timestamp()'::timestamp;
ERROR: invalid input syntax for type timestamp: "clock_timestamp()"
LINE 1: select clock_timestamp()::timestamp, 'clock_timestamp()'::ti...
^
Why is NOW() so special? Where is it documented? And why not working with other timestamp returning internal functions?
> select version();
version
--------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.4 on amd64-portbld-freebsd10.0, compiled by FreeBSD clang version 3.3 (tags/RELEASE_33/final 183502) 20130610, 64-bit
(1 wiersz)
Piotr Gasidło
On 14/08/22 15:40, Piotr Gasidło wrote: > Hello, > > I found strange PostgreSQL 9.3 behavior: > >> select now()::timestamp, 'now()'::timestamp; > now | timestamp > ----------------------------+---------------------------- > 2014-08-22 08:34:00.883268 | 2014-08-22 08:34:00.883268 > > Second column is now() in single apostrophes. > > Now, I tried similar function, clock_timestamp() and get: > >> select clock_timestamp()::timestamp, 'clock_timestamp()'::timestamp; > ERROR: invalid input syntax for type timestamp: "clock_timestamp()" > LINE 1: select clock_timestamp()::timestamp, 'clock_timestamp()'::ti... > ^ > > Why is NOW() so special? Where is it documented? Here: http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT "All the date/time data types also accept the special literal value 'now' to specify the current date and time" and also here: http://www.postgresql.org/docs/9.3/static/datatype-datetime.html#AEN5861 Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Piotr Gasidło wrote > Hello, > > I found strange PostgreSQL 9.3 behavior: > >> select now()::timestamp, 'now()'::timestamp; > now | timestamp > ----------------------------+---------------------------- > 2014-08-22 08:34:00.883268 | 2014-08-22 08:34:00.883268 > > Second column is now() in single apostrophes. > > Now, I tried similar function, clock_timestamp() and get: > >> select clock_timestamp()::timestamp, 'clock_timestamp()'::timestamp; > ERROR: invalid input syntax for type timestamp: "clock_timestamp()" > LINE 1: select clock_timestamp()::timestamp, 'clock_timestamp()'::ti... > ^ > > Why is NOW() so special? Where is it documented? And why not working with > other timestamp returning internal functions? > >> select version(); > version > > -------------------------------------------------------------------------------------------------------------------------------------- > PostgreSQL 9.3.4 on amd64-portbld-freebsd10.0, compiled by FreeBSD clang > version 3.3 (tags/RELEASE_33/final 183502) 20130610, 64-bit > (1 wiersz) > > > -- > Piotr Gasidło SELECT ' now** '::timestamp --works Pretty much any symbol before or after the word now is allowed and you still get a valid result. Putting a letter or number anywhere in the string causes an input syntax error. Tested on 9.0 As for documentation: http://www.postgresql.org/docs/9.2/interactive/datetime-input-rules.html 2.b 'now' is a "special string" as referenced in this rule The tokenizer must be constructed to throw away whitespace and any symbols except those used in normal timestamps (~ [:/-]) <tests 'now-'> Yep, ^ gives me an error. That appendix section is missing considerable detail that I've inferred from the observed behavior - though some of the gaps are filled in once you've read the following: http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html The above also explains that the special SQL keywords cannot be used as string literals though as is often the case it omits any discussion as to why. The fact that they are functions obviously does not preclude them from also being keywords... Most likely its this way for SQL standards compatibility reasons. Do you have a use-case you'd like to share or is this curiosity after accidentally finding out that 'now'::timestamp actually works? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SELECT-NOW-OK-SELECT-CLOCK-TIMESTAMP-ERROR-tp5815823p5815826.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi
2014-08-22 9:05 GMT+02:00 David G Johnston <david.g.johnston@gmail.com>:
Piotr Gasidło wrote
> Hello,
>
> I found strange PostgreSQL 9.3 behavior:
>
>> select now()::timestamp, 'now()'::timestamp;
> now | timestamp
> ----------------------------+----------------------------
> 2014-08-22 08:34:00.883268 | 2014-08-22 08:34:00.883268
>
> Second column is now() in single apostrophes.
>
> Now, I tried similar function, clock_timestamp() and get:
>
>> select clock_timestamp()::timestamp, 'clock_timestamp()'::timestamp;
> ERROR: invalid input syntax for type timestamp: "clock_timestamp()"
> LINE 1: select clock_timestamp()::timestamp, 'clock_timestamp()'::ti...
> ^
>
> Why is NOW() so special? Where is it documented? And why not working with
> other timestamp returning internal functions?
>
>> select version();
> version
>
> --------------------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.3.4 on amd64-portbld-freebsd10.0, compiled by FreeBSD clang
> version 3.3 (tags/RELEASE_33/final 183502) 20130610, 64-bit
> (1 wiersz)
>
>
> --
> Piotr Gasidło
SELECT ' now** '::timestamp --works
Pretty much any symbol before or after the word now is allowed and you still
get a valid result. Putting a letter or number anywhere in the string
causes an input syntax error.
Tested on 9.0
As for documentation:
http://www.postgresql.org/docs/9.2/interactive/datetime-input-rules.html
2.b
'now' is a "special string" as referenced in this rule
The tokenizer must be constructed to throw away whitespace and any symbols
except those used in normal timestamps (~ [:/-])
<tests 'now-'>
Yep, ^ gives me an error.
That appendix section is missing considerable detail that I've inferred from
the observed behavior - though some of the gaps are filled in once you've
read the following:
http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html
The above also explains that the special SQL keywords cannot be used as
string literals though as is often the case it omits any discussion as to
why. The fact that they are functions obviously does not preclude them from
also being keywords...
Most likely its this way for SQL standards compatibility reasons.
Do you have a use-case you'd like to share or is this curiosity after
accidentally finding out that 'now'::timestamp actually works?
David J.
there are more than "now"
postgres=# select 'now'::timestamp;
timestamp
----------------------------
2014-08-22 09:08:26.956702
(1 row)
postgres=# select 'tomorrow'::timestamp;
timestamp
---------------------
2014-08-23 00:00:00
(1 row)
postgres=# select 'today'::timestamp;
timestamp
---------------------
2014-08-22 00:00:00
(1 row)
postgres=# select 'yesterday'::timestamp;
timestamp
---------------------
2014-08-21 00:00:00
(1 row)
postgres=# select 'now'::timestamp;
timestamp
----------------------------
2014-08-22 09:08:26.956702
(1 row)
postgres=# select 'tomorrow'::timestamp;
timestamp
---------------------
2014-08-23 00:00:00
(1 row)
postgres=# select 'today'::timestamp;
timestamp
---------------------
2014-08-22 00:00:00
(1 row)
postgres=# select 'yesterday'::timestamp;
timestamp
---------------------
2014-08-21 00:00:00
(1 row)
Regards
Pavel
--
View this message in context: http://postgresql.1045698.n5.nabble.com/SELECT-NOW-OK-SELECT-CLOCK-TIMESTAMP-ERROR-tp5815823p5815826.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2014-08-22 9:05 GMT+02:00 David G Johnston <david.g.johnston@gmail.com>:
Do you have a use-case you'd like to share or is this curiosity after
accidentally finding out that 'now'::timestamp actually works?
I've found it by accident and I had to ask if it is bug or right way.
Piotr Gasidło