[
https://issues.apache.org/jira/browse/FLINK-6813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16689520#comment-16689520
]
ASF GitHub Bot commented on FLINK-6813:
---------------------------------------
twalthr commented on issue #4117: [FLINK-6813][table]Add TIMESTAMPDIFF
supported in SQL
URL: https://github.com/apache/flink/pull/4117#issuecomment-439418909
This issue has been in FLINK-6847. I will close this PR.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
> 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
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.7.0
>
>
> 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|https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff]
> 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}
> 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.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
