Re: [GENERAL] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR
2014-08-22 9:05 GMT+02:00 David G Johnston david.g.johns...@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
Re: [GENERAL] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR
Hi 2014-08-22 9:05 GMT+02:00 David G Johnston david.g.johns...@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) 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
