I found this: https://stackoverflow.com/questions/26138167/is-timestampdiff-in-mysql-equivalent-to-datediff-in-sql-server
We want Calcite's implementation of TIMESTAMPDIFF to be consistent with MySQL. If it's not, that's a bug in our TIMESTAMPDIFF. Frankly I don't want to think too much about these functions; I just want to be compatible with the "standard" implementations. If DATEDIFF and TIMESTAMPDIFF have different behavior, maybe we should re-visit https://issues.apache.org/jira/browse/CALCITE-1827 and implement DATEDIFF after all. Julian On Thu, Jun 8, 2017 at 10:54 PM, jincheng sun <[email protected]> wrote: > Hi, Julian, > When I using TIMESTAMPDIFF, run the sql as follows: > > SELECT > timestampdiff(WEEK, timestamp '2019-06-01 07:01:11',timestamp '2020-06-01 > 07:01:11'), > timestampdiff(WEEK, timestamp '2020-06-01 07:01:11',timestamp '2021-06-01 > 07:01:11') > FROM depts limit 1; > > I get the result : | 52 | 52 | > > And I check it in the MSSQL: > > SELECT > datediff(WEEK, '2019-06-01 07:01:11','2020-06-01 07:01:11'), > datediff(WEEK, '2020-06-01 07:01:11', '2021-06-01 07:01:11') > FROM stu; > > I get the result: |53 |52 > > As we know if the year starts on a week in a non-leap year, you end up with > 53 weeks. Or if either of the first two days lands on a week during a leap > year, then you can also get 53 weeks. > > So, I want to know why design `TIMESTAMPDIFF` as above logic. Please tell > me more about it. > > Thanks, > SunJincheng
