[FEATURE] Support date and time functions

[Yury-Fridlyand]

Copied from opendistro-for-elasticsearch/sql#709.

Implementation should match MySQL standard: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

• date - Update DATE and TIME functions to parse string input as datetime #991
• time
• timestamp
• adddate
• date_add
• date_format
• date_sub
• dayname
• dayofweek
• dayofmonth
• dayofyear
• from_days
• makedate - Add maketime and makedate datetime functions #755
• maketime- Add maketime and makedate datetime functions #755
• monthname
• hour
• minute
• second
• microsecond
• day
• week
• month
• quarter
• year
• subdate
• time_to_sec
• to_days
• addtime - Add functions ADDTIME and SUBTIME. (#132) #1194
• adddate (update) - Update DATE_ADD/ADDDATE and DATE_SUB/SUBDATE functions. (#122) #1182
• convert_tz - Adding CONVERT_TZ and DATETIME functions to SQL and PPL  #848
• curdate - Add implementation of now, sysdate, localtime and similar functions #754
• current_date - Add implementation of now, sysdate, localtime and similar functions #754
• current_time - Add implementation of now, sysdate, localtime and similar functions #754
• current_timestamp - Add implementation of now, sysdate, localtime and similar functions #754
• curtime - Add implementation of now, sysdate, localtime and similar functions #754
• datediff - Add TIMEDIFF and DATEDIFF functions. (#131) #1195
• datetime - Adding CONVERT_TZ and DATETIME functions to SQL and PPL  #848
• date_add (update) - Update DATE_ADD/ADDDATE and DATE_SUB/SUBDATE functions. (#122) #1182
• date_sub (update) - Update DATE_ADD/ADDDATE and DATE_SUB/SUBDATE functions. (#122) #1182
• day_of_month - Add Day_Of_Month Function As An Alias Of DayOfMonth #1227
• day_of_week - Add Day_Of_Week Function As An Alias Of DayOfWeek #1228
• day_of_year - Add Day_Of_Year Function To OpenSearch #1128
• extract - Add EXTRACT Function To OpenSearch SQL Plugin #1421
• hour_of_day - Add Hour_Of_Day Function As An Alias Of Hour #1226
• from_unixtime - Add datetime functions FROM_UNIXTIME and UNIX_TIMESTAMP #835
• get_format - Add GET_FORMAT Function To OpenSearch SQL Plugin #1299
• get_format more formats - Support More Formats For GET_FORMAT Function #1343
• last_day - Add last_day Function To OpenSearch SQL Plugin #1344
• localtime - Add implementation of now, sysdate, localtime and similar functions #754
• localtimestamp - Add implementation of now, sysdate, localtime and similar functions #754
• minute_of_day - Add Minute_Of_Day Function To SQL Plugin #1207
• minute_of_hour - Add Minute_Of_Hour Function As An Alias Of Minute Function (#196) #1230
• month_of_year - Add Month_Of_Year Function To OpenSearch #1129
• now - Add implementation of now, sysdate, localtime and similar functions #754
• period_add - Add functions PERIOD_ADD and PERIOD_DIFF. #933
• period_diff - Add functions PERIOD_ADD and PERIOD_DIFF. #933
• sec_to_time - Add sec_to_time Function To OpenSearch SQL #1378
• second_of_minute - Add Second_Of_Minute Function As An Alias Of The Second Function #1231
• str_to_date - Add STR_TO_DATE Function To The SQL Plugin #1420
• subdate (update) - Update DATE_ADD/ADDDATE and DATE_SUB/SUBDATE functions. (#122) #1182
• subtime - Add functions ADDTIME and SUBTIME. (#132) #1194
• sysdate - Add implementation of now, sysdate, localtime and similar functions #754
• time - Update DATE and TIME functions to parse string input as datetime #991
• timediff/time_diff - Add TIMEDIFF and DATEDIFF functions. (#131) #1195
• timestamp - Update TIMESTAMP function implementation and signatures. #1254
• timestampadd - Add TIMESTAMPADD Function To OpenSearch SQL Plugin #1451
• timestampdiff - Add Timestampdiff Function To OpenSearch SQL #1467
• time_format - Add TIME_FORMAT() Function To SQL Plugin #1301
• to_seconds - Add The TO_SECONDS Function To The SQL Plugin #1419
• unix_timestamp - Add datetime functions FROM_UNIXTIME and UNIX_TIMESTAMP #835
• utc_date - Adding UTC_DATE, UTC_TIME, UTC_TIMESTAMP #1193
• utc_time - Adding UTC_DATE, UTC_TIME, UTC_TIMESTAMP #1193
• utc_timestamp - Adding UTC_DATE, UTC_TIME, UTC_TIMESTAMP #1193
• weekday - Add WEEKDAY Function to SQL Plugin #1418
• weekofyear - Add WeekOfYear Function To OpenSearch #1345
• week_of_year - Add Week_Of_Year Function To OpenSearch #1127
• yearweek - Add YEARWEEK Function To OpenSearch SQL #1417
• >=/>/=/!=/</<= (comparison operators for datetime types) - Extend comparison methods to accept different datetime types. (#129) #1196
• min/max/avg aggregations on datetime types - Datetime aggregation fixes. #1061
• [BUG] DATE_FORMAT Function Missing Support For 3rd Optional Field For Timezone #1436

Further improvements:

• [FEATURE] Add 3 interval types instead of 1 to support complex interval expressions #859
• [FEATURE] Add new data type: UNIT #861
• [FEATURE] Add new data type: PERIOD #862
• [BUG] Fix CAST ( ... AS DATETIME) #853 (Fix type cast to datetime #1328)
• [Discussion] Do we need to support more than 24 hours in TIME? #852
• [Discussion] SQL dialect #855
• [BUG] SQL query doesn't honor date format in OpenSearch index mapping #794/[BUG] Allowing date in format yyyy-MM-dd HH:mm:ss.SSSSSS #126

[GabeFernandez310]

Date_format may need to be fixed to deprecate legacy. There are legacy tests which fail in the new engine if legacy is disabled (possibly due to the lack of support for a 3rd argument for a timezone).

These include the following in DateFormatIT:

• DateFormatIT. and
• DateFormatIT. equalTo
• DateFormatIT. greaterThan
• DateFormatIT. greaterThanOrEqualTo
• DateFormatIT. lessThan
• DateFormatIT. lessThanOrEqualTo
• DateFormatIT. or
• DateFormatIT. sortByDateFormat

[Yury-Fridlyand]

Closed as completed.