the date() function is undocumented while quite necessary

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

the date() function is undocumented while quite necessary

PG Doc comments form
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/12/functions-datetime.html
Description:

I can't find anywhere the documentation of the SQL DATE() function which
extracts the date part out of a timestamp.

Please note that according to my tests, one can't use the cast operator when
defining expression-based indexes, while the date(column) expression is
accepted. It makes the DATE() function way more useful than one may think.
Reply | Threaded
Open this post in threaded view
|

Re: the date() function is undocumented while quite necessary

Tom Lane-2
PG Doc comments form <[hidden email]> writes:
> I can't find anywhere the documentation of the SQL DATE() function which
> extracts the date part out of a timestamp.

It's a type cast written in function-like syntax, as documented in

https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

We don't really encourage that notation, because it's confusing
and it doesn't work for every possible type name, which is why
you won't find it mentioned very many places.

> Please note that according to my tests, one can't use the cast operator when
> defining expression-based indexes, while the date(column) expression is
> accepted.

You can if you put parentheses around it:

regression=# create table t1 (f1 timestamp);
CREATE TABLE
regression=# create index on t1 (f1::date);
ERROR:  syntax error at or near "::"
LINE 1: create index on t1 (f1::date);
                              ^
regression=# create index on t1 ((f1::date));
CREATE INDEX

This is per the restriction explained in the CREATE INDEX docs, that
any expression-to-be-indexed that doesn't look like a function call
requires parentheses.

https://www.postgresql.org/docs/current/indexes-expressional.html

https://www.postgresql.org/docs/current/sql-createindex.html

                        regards, tom lane