Thanks for your reply. You are right! DATEDIFF and TIMESTAMPDIFF are just "similar" functions implemented by different DBs, and we do not have to impose them as consistent. Now that I know they have a different reason, it's enough for me. If one day we really need DATAADD / DIFF function, DATEADD / DIFF and TIMESTAMPADD / DIFF can co-exist in calcite.
Thanks, SunJIncheng 2017-06-10 2:02 GMT+08:00 Julian Hyde <[email protected]>: > 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 >
