[ https://issues.apache.org/jira/browse/FLINK-6813?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
sunjincheng updated FLINK-6813: ------------------------------- Description: * Syntax TIMESTAMPDIFF ( datepart , startdate , enddate ) -datepart Is the part of startdate and enddate that specifies the type of boundary crossed. -startdate Is an expression that can be resolved to a time, date. -enddate Same with startdate. * Example SELECT TIMESTAMPDIFF(year, '2015-12-31 23:59:59.9999999', '2017-01-01 00:00:00.0000000') from tab; --> 2 CALCITE: {code} 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 {code} MSSQL: {code} 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 {code} See more: [https://issues.apache.org/jira/browse/CALCITE-1827|https://issues.apache.org/jira/browse/CALCITE-1827] was: * Syntax TIMESTAMPDIFF ( datepart , startdate , enddate ) -datepart Is the part of startdate and enddate that specifies the type of boundary crossed. -startdate Is an expression that can be resolved to a time, date. -enddate Same with startdate. * Example SELECT TIMESTAMPDIFF(year, '2015-12-31 23:59:59.9999999', '2017-01-01 00:00:00.0000000') from tab; --> 2 CALCITE: {code} 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; +------------+------------+------------+------------+------------+-------------+ | EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | +------------+------------+------------+------------+------------+-------------+ | 1 | 4 | 12 | 52 | 366 | 8784 | +------------+------------+------------+------------+------------+-------------+ {code} MSSQL: {code} 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 {code} See more: [https://issues.apache.org/jira/browse/CALCITE-1827|https://issues.apache.org/jira/browse/CALCITE-1827] > Add TIMESTAMPDIFF supported in SQL > ---------------------------------- > > Key: FLINK-6813 > URL: https://issues.apache.org/jira/browse/FLINK-6813 > Project: Flink > Issue Type: Sub-task > Components: Table API & SQL > Affects Versions: 1.4.0 > Reporter: sunjincheng > Assignee: sunjincheng > > * Syntax > TIMESTAMPDIFF ( datepart , startdate , enddate ) > -datepart > Is the part of startdate and enddate that specifies the type of boundary > crossed. > -startdate > Is an expression that can be resolved to a time, date. > -enddate > Same with startdate. > * Example > SELECT TIMESTAMPDIFF(year, '2015-12-31 23:59:59.9999999', '2017-01-01 > 00:00:00.0000000') from tab; --> 2 > CALCITE: > {code} > 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 > {code} > MSSQL: > {code} > 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 > {code} > See more: > [https://issues.apache.org/jira/browse/CALCITE-1827|https://issues.apache.org/jira/browse/CALCITE-1827] -- This message was sent by Atlassian JIRA (v6.3.15#6346)