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 Mon, 15 Jan 2018 12:31:02 GMT

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

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

Github user sunjincheng121 commented on a diff in the pull request:

    https://github.com/apache/flink/pull/4117#discussion_r161504809
  
    --- Diff: flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/ScalarOperators.scala
---
    @@ -702,12 +703,15 @@ object ScalarOperators {
       def generateTemporalPlusMinus(
           plus: Boolean,
           nullCheck: Boolean,
    +      typeName: SqlTypeName,
           left: GeneratedExpression,
           right: GeneratedExpression)
         : GeneratedExpression = {
     
         val op = if (plus) "+" else "-"
     
    +    val AVGDAYS_PRE_MONTH = 30.5
    --- End diff --
    
    Is not a standard, I did not find the relevant norms, do you have any suggestions?


> 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 &amp; SQL
>    Affects Versions: 1.4.0
>            Reporter: sunjincheng
>            Assignee: sunjincheng
>            Priority: Major
>
> 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)

Mime
View raw message