[FLINK-13561] Verify and correct time function's semantic for Blink planner

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.9.0
• Component/s: Table SQL / Planner
• Labels: pull-request-available
• Assignee: Jark Wu
• Reporter: Jingsong Lee
• Created: 02/Aug/19 13:55
• Updated: 01/Apr/20 06:57
• Resolved: 08/Aug/19 12:47

Description

• Drop CONVERT_TZ(timestamp, format, from_tz, to_tz) function support
• Drop CONVERT_TZ(timestamp, format, from_tz, to_tz) function support in blink planner to align with other databases. We only support CONVERT_TZ(timestamp, from_tz, to_tz) in this version.

• Drop DATE_FORMAT(timestamp, from_format, to_format) function support
• Drop DATE_FORMAT(timestamp, from_format, to_format) function support in blink planner to align with other databases. We only support DATE_FORMAT(timestamp, to_format) and DATE_FORMAT(string, to_format) in this version.

• Drop TO_DATE(int) function support
• Drop TO_DATE(int) function support in blink planner to align with other databases. We only support TO_DATE(string [,format]) in this version.

• Drop TO_TIMESTAMP(bigint) function support
• Drop TO_TIMESTAMP(bigint) function support in blink planner to align with other systems. We only support TO_TIMESTAMP(string [,format]) in this version.

• Remove some builtin datetime functions which can be covered by existing functions
• Removes DATE_FORMAT_TZ, DATE_ADD,DATE_SUB, DATEDIFF, FROM_TIMESTAMP, TO_TIMESTAMP_TZ

• Fix FROM_UNIXTIME(bigint [,format]) should work in session time zone
• This aligns the behavior to other systems (MySQL, Spark).

• Fix UNIX_TIMESTAMP(string [,format]) should work in session time zone
• This aligns the behavior to other systems (MySQL, Spark). UNIX_TIMESTAMP(string [,format]) is an inverse of FROM_UNIXTIME(bigint [,format]). We also remove the support of UNIX_TIMESTAMP(timestamp) in this commit.

• Fix NOW() should return TIMESTAMP instead of BIGINT.
• This aligns the behavior to other systems (MySQL, Spark). Because NOW() is Synonyms for CURRENT_TIMESTAMP.

Issue Links

• relates to: FLINK-16889 Support converting BIGINT to TIMESTAMP for TO_TIMESTAMP function
• links to: GitHub Pull Request #9377