the date() function is undocumented while quite necessary
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.
PG Doc comments form <noreply@postgresql.org> 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