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:16:00 GMT

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

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

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

    https://github.com/apache/flink/pull/4117#discussion_r123714418
  
    --- Diff: flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/CodeGenerator.scala
---
    @@ -1367,7 +1367,7 @@ class CodeGenerator(
             val right = operands(1)
             requireTemporal(left)
             requireTemporal(right)
    -        generateTemporalPlusMinus(plus = false, nullCheck, left, right)
    +        generateTemporalPlusMinus(plus = false, nullCheck, call.`type`.getSqlTypeName,
left, right)
    --- End diff --
    
    I think you don't need to change this method. You can do the match with the `TimeIntervalTypeInfo`.


> 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