flink-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (FLINK-6813) Add TIMESTAMPDIFF supported in SQL
Date Fri, 23 Jun 2017 10:15:00 GMT

    [ https://issues.apache.org/jira/browse/FLINK-6813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16060685#comment-16060685
] 

ASF GitHub Bot commented on FLINK-6813:
---------------------------------------

Github user twalthr commented on the issue:

    https://github.com/apache/flink/pull/4117
  
    Thanks for the PR @sunjincheng121. I had just minor comments. Could you also add this
function to the Table API in Scala/Java? It should not be much work. Can you also add some
documentation for both SQL and Table API?


> 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)

Mime
View raw message