[FLINK-6813] Add TIMESTAMPDIFF supported in SQL

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.4.0
• Fix Version/s: 1.7.0
• Component/s: Table SQL / API
• Labels: pull-request-available
• Assignee: sunjincheng
• Reporter: sunjincheng
• Created: 02/Jun/17 08:37
• Updated: 16/Nov/18 14:58
• Resolved: 25/Sep/18 17:18

Description

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.

• Syntax
  TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
  -unit
  Is the part of datetime_expr1 and datetime_expr2 that specifies the type of boundary crossed.
  -datetime_expr1
  Is an expression that can be resolved to a time, date.
  -datetime_expr2
  Same with startdate.
• Example
  SELECT TIMESTAMPDIFF(year, '2015-12-31 23:59:59.9999999', '2017-01-01 00:00:00.0000000') from tab; --> 2

• See more:
  MySQL

CALCITE:

 SELECT timestampdiff(YEAR, timestamp '2019-06-01 07:01:11', timestamp '2020-06-01 07:01:11'),timestampdiff(QUARTER, timestamp '2019-06-01 07:01:11', timestamp '2020-06-01 07:01:11'),timestampdiff(MONTH, timestamp '2019-06-01 07:01:11',timestamp '2020-06-01 07:01:11'),timestampdiff(WEEK, timestamp '2019-06-01 07:01:11',timestamp '2020-06-01 07:01:11'),timestampdiff(DAY, timestamp '2019-06-01 07:01:11',timestamp '2020-06-01 07:01:11'),timestampdiff(HOUR, timestamp '2019-06-01 07:01:11',timestamp '2020-06-01 07:01:11'),timestampdiff(MINUTE, timestamp '2019-06-01 07:01:11',timestamp '2020-06-01 07:01:11'),timestampdiff(SECOND, timestamp '2019-06-01 07:01:11',timestamp '2020-06-01 07:01:11') FROM depts;
| 1     | 4     | 12     | **52**     | 366    | 8784    | 527040     | 31622400  

MSSQL:

SELECT
  datediff(YEAR, '2019-06-01 07:01:11','2020-06-01 07:01:11'),
  datediff(QUARTER, '2019-06-01 07:01:11', '2020-06-01 07:01:11'),
  datediff(MONTH, '2019-06-01 07:01:11','2020-06-01 07:01:11'),
  datediff(WEEK, '2019-06-01 07:01:11', '2020-06-01 07:01:11'),
  datediff(DAY, '2019-06-01 07:01:11','2020-06-01 07:01:11'),
  datediff(HOUR, '2019-06-01 07:01:11','2020-06-01 07:01:11'),
  datediff(MINUTE, '2019-06-01 07:01:11','2020-06-01 07:01:11'),
  datediff(SECOND,  '2019-06-01 07:01:11', '2020-06-01 07:01:11')
FROM stu;
|1	|4	|12	|**53**	|366	|8784	|527040	|31622400

The differences I have discussed with the calcite community. And find the reason: https://stackoverflow.com/questions/26138167/is-timestampdiff-in-mysql-equivalent-to-datediff-in-sql-server.
So, In this JIRA. we will keep consistency with calcite.

Issue Links

• blocks: FLINK-6847 Add TIMESTAMPDIFF supported in TableAPI
• links to: GitHub Pull Request #4117
• links to: GitHub Pull Request #4117