sql: support postgresql date() function to convert timestamp to date

[mowinslow2]

The DATE() function in PostgreSQL allows you to extract a date value from a timestamp.

This syntax looks like the following:

SELECT DATE(timestamp_column) FROM table_name;
date
----------
2023-02-13

Alternative solutions would be to just cast to a date, but this requires a code change for existing applications.

SELECT timestamp_column::DATE FROM table_name;

Another solution could be a UDF, but this is scoped by the schema/namespace.

I think the ideal solution would be to have this function native in Cockroach since it is native to PostgreSQL. One interesting thing I'll point out is that I could not find any documentation of this function in the Postgres docs.

Jira issue: CRDB-24488

[otan]

The real fix here is that all type names are functions and should be used as a cast, eg DATE(COL) casts col to a DATE.

I can't find the issue I may have made to make this work, but iirc it wasn't easy cause our Parser hard codes types

[ZhouXing19]

Adding to Oliver's comment -- here's Postgres's list for mapping a cast to a function: https://github.com/postgres/postgres/blob/e9a20e451f3aa0b64da807338012c65d8664d0ac/src/include/catalog/pg_cast.dat#L351-L377

[otan]

#97093 is a crude fix for it, but not sure if we prefer to do it in type resolution.