Re: SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR

Hi

2014-08-22 9:05 GMT+02:00 David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>