What is the bug?
date_format does not fully work as it does in legacy. There are legacy tests which fail in the new engine if legacy is disabled due to the lack of support for a 3rd argument for a timezone. This causes a date_format query to not be properly parsed.

Failing IT tests include the following in DateFormatIT.java which have been disabled for now (Tests here):
DateFormatIT. and
DateFormatIT. equalTo
DateFormatIT. greaterThan
DateFormatIT. greaterThanOrEqualTo
DateFormatIT. lessThan
DateFormatIT. lessThanOrEqualTo
DateFormatIT. or
DateFormatIT. sortByDateFormat

How can one reproduce the bug?
Steps to reproduce the behavior:

1. Checkout the branch found here
2. Build the plugin
3. Install onto an OpenSearch cluster
4. Run the cluster
5. Execute the following curl command curl --location 'http://localhost:9200/_plugins/_sql?format=csv' \ --header 'Content-Type: application/json' \ --data '{ "query": "SELECT time0 FROM calcs WHERE DATE_FORMAT(time0, '\''yyyy-MM-dd'\'', '\''UTC'\'') < '\''2014-08-18'\''" }'
6. See the following error message:

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "date_format function expected {[STRING,STRING],[DATE,STRING],[DATETIME,STRING],[TIME,STRING],[TIMESTAMP,STRING]}, but get [TIMESTAMP,STRING,STRING]",
    "type": "ExpressionEvaluationException"
  },
  "status": 400

What is the expected behavior?
The query should be parsed correctly and should return something similar to the following:

time0
1899-12-30T21:07:32Z
1900-01-01T13:48:48Z
1900-01-01T18:21:08Z
1900-01-01T18:51:48Z
1900-01-01T15:01:19Z
1900-01-01T08:59:39Z
1900-01-01T07:37:48Z
1900-01-01T19:45:54Z
1900-01-01T09:00:59Z
1900-01-01T20:36:00Z
1900-01-01T01:31:32Z
1899-12-30T22:15:40Z
1900-01-01T13:53:46Z
1900-01-01T04:57:51Z
1899-12-30T22:42:43Z
1899-12-30T22:24:08Z
1900-01-01T11:58:29Z%

This is what is returned by legacy when running the following curl command

curl --location 'http://localhost:9200/_plugins/_sql?format=csv' \
--header 'Content-Type: application/json' \
--data '{
    "fetch_size" : 1,
    "query": "SELECT time0 FROM calcs WHERE DATE_FORMAT(time0, '\''yyyy-MM-dd'\'', '\''UTC'\'') < '\''2014-08-18'\''"
}'

What is your host/environment?

• OS: macOS
• Version: Ventura 13.2.1
• Plugins: SQL Plugin Version 3.0

Do you have any screenshots?

Do you have any additional context?
May not need to be supported. There are alternate functions available to convert timezones, but it is a gap with legacy. This argument also does not exist for MySQL or other DBs with support for a similar function as far as I have seen.