Date time query improvement

[dai-chen]

Is your feature request related to a problem? Please describe.
Many date functions are already supported though some common ones are still missing (in new engine). Meanwhile time zone support is also high demand.

Here are the feature requests from community:

1. Functions: Support date and time functions for new engine opendistro-for-elasticsearch/sql#709
   1.1 Year(MyDateField) <= YEAR(NOW()) not supported opendistro-for-elasticsearch/sql#1073: NOW()
2. Time zone: Timezone issue when retrieving date from Elasticsearch (UTC considered as local) opendistro-for-elasticsearch/sql#1062

Describe the solution you'd like
N/A

Describe alternatives you've considered
N/A

Additional context
Add any other context or screenshots about the feature request here.

[dai-chen]

Another customer reported issue in using NOW() function with other date functions: https://discuss.opendistrocommunity.dev/t/subdate-date-sub-query-method-not-supported/9252/2. We need to improve the common date function support in new engine to avoid fallback to legacy engine without complete expression system.

[dredwilliams]

It also appears that adddate/subdate functions don't work properly in WHERE clauses -- even when used with fields instead of the now() function:

SELECT now() as n, @timestamp, nodename, state FROM node-index WHERE adddate(@timestamp, INTERVAL 5 MINUTE) > n

The statement works until I add the where clause, at which point I get the "unsupported method: adddate" error message

[dai-chen]

Support for more date format: #580

[penghuo]

[BUG] Aggregate on date function does not work as expected. #645

[acarbonetto]

List of improvements, prioritized:

• date
• time
• timestamp
• microsecond
• second
• minute
• hour
• day
• week
• month
• quarter
• year
• adddate
• date_add
• date_format
• date_sub
• dayname
• dayofweek
• dayofmonth
• dayofyear
• from_days
• monthname
• subdate
• time_to_sec
• to_days
• addtime
• convert_tz
• curdate
• current_date
• current_time
• current_timestamp
• curtime
• datediff
• extract
• from_unixtime
• get_format
• last_day
• localtime
• localtimestamp
• makedate
• maketime
• now
• period_add
• period_diff
• sec_to_time
• str_to_date
• subtime
• sysdate
• time_format
• timediff
• timestampadd
• timestampdiff
• to_seconds
• unix_timestamp
• utc_date
• utc_time
• utc_timestamp
• weekday
• weekofyear
• yearweek

[acarbonetto]

Need to add to the above list:

• day_of_year
• month_of_year
• hour_of_day
• week_of_year
• day_of_month
• minute_of_day
• minute_of_hour
• second_of_minute

[acarbonetto]

Will be partially resolved by: #722

[acarbonetto]

Closing this. Only bug remaining is the timezone issue: #1874