You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "ASF GitHub Bot (JIRA)" <ji...@apache.org> on 2017/07/18 08:40:00 UTC
[jira] [Commented] (FLINK-6813) Add TIMESTAMPDIFF supported in SQL
[ https://issues.apache.org/jira/browse/FLINK-6813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16091291#comment-16091291 ]
ASF GitHub Bot commented on FLINK-6813:
---------------------------------------
Github user twalthr commented on the issue:
https://github.com/apache/flink/pull/4117
@sunjincheng121 this PR would be ready to be updated.
> 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
>
> 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
(v6.4.14#64029)