[
https://issues.apache.org/jira/browse/FLINK-6813?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
sunjincheng updated FLINK-6813:
-------------------------------
Description:
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.
was:
* Syntax
TIMESTAMPDIFF ( datepart , startdate , enddate )
-datepart
Is the part of startdate and enddate that specifies the type of boundary
crossed.
-startdate
Is an expression that can be resolved to a time, date.
-enddate
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.
> 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)
