Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1827

Document TIMESTAMPADD, TIMESTAMPDIFF functions

    Details

    • Type: New Feature
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.13.0
    • Component/s: core
    • Labels:
      None

      Description

      Document TIMESTAMPADD, TIMESTAMPDIFF functions.

      (Initial request was to add DATEADD as built-in scalar function, as follows. But it turns out that TIMESTAMPADD is similar enough.)

      Syntax: DATEADD (datepart , number , date )

      Arguments:

      • datepart - Is the part of date to which an integer number is added.
      • number - Is an expression that can be resolved to an int that is added to a datepart of date
      • date - Is an expression that can be resolved to a time.

      Example

      SELECT DATEADD(month, 1, '2017-05-31') from tab;

      returns 2017-06-30 00:00:00.000

      MSSQL: https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql
      MySql: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-add

        Activity

        Hide
        jcamachorodriguez Jesus Camacho Rodriguez added a comment -

        Resolved in release 1.13.0 (2017-06-26).

        Show
        jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.13.0 (2017-06-26).
        Hide
        julianhyde Julian Hyde added a comment -

        Added documentation for TIMESTAMPADD, TIMESTAMPDIFF in http://git-wip-us.apache.org/repos/asf/calcite/commit/f5df5dd8. Thanks for the PR, sunjincheng!

        Show
        julianhyde Julian Hyde added a comment - Added documentation for TIMESTAMPADD, TIMESTAMPDIFF in http://git-wip-us.apache.org/repos/asf/calcite/commit/f5df5dd8 . Thanks for the PR, sunjincheng !
        Hide
        julianhyde Julian Hyde added a comment - - edited

        The bug is CALCITE-1639, and it will be fixed in 1.13. If you need a hot fix, speak to the company from which you buy support. You can apply the pull request in your own build if you need a patch urgently. Otherwise wait for 1.13.

        If TIMESTAMPADD is similar enough to DATEADD for your purposes, I'll close this case.

        Show
        julianhyde Julian Hyde added a comment - - edited The bug is CALCITE-1639 , and it will be fixed in 1.13. If you need a hot fix, speak to the company from which you buy support. You can apply the pull request in your own build if you need a patch urgently. Otherwise wait for 1.13. If TIMESTAMPADD is similar enough to DATEADD for your purposes, I'll close this case.
        Hide
        sunjincheng121 sunjincheng added a comment -

        From user point of view, TIMESTAMPADD can covers the current semantics of DATEADD.So
        I think TIMESTAMPADD is enough. What do you think?

        Another thing:
        I find about TIMESTAMPADD the difference between calcite 1.12 and calcite master affects the semantics of the build-in scalar function, for example:
        The SQL:

        select 
        timestampadd(MONTH, 1,  date '2016-05-31'), 
        timestampadd(MONTH, 5,  date '2016-01-31'), 
        timestampadd(MONTH, -1, date '2016-03-31') from depts;
        

        Calcite 1.12 Result:

        +--------+--------+--------+
        | EXPR$0 | EXPR$1 | EXPR$2 |
        +--------+--------+--------+
        | 2016-07-01 | 2016-07-01 | 2016-03-01 |
        +--------+--------+--------+
        3 rows selected (1.06 seconds)
        

        Calcite Master result:

        +--------+--------+--------+
        | EXPR$0 | EXPR$1 | EXPR$2 |
        +--------+--------+--------+
        | 2016-06-30 | 2016-06-30 | 2016-02-29 |
        +--------+--------+--------+
        3 rows selected (1.047 seconds)
        

        I think we need some hot fix for calcite 1.12. What do you think?
        Best,
        SunJincheng

        Show
        sunjincheng121 sunjincheng added a comment - From user point of view, TIMESTAMPADD can covers the current semantics of DATEADD .So I think TIMESTAMPADD is enough. What do you think? Another thing: I find about TIMESTAMPADD the difference between calcite 1.12 and calcite master affects the semantics of the build-in scalar function, for example: The SQL: select timestampadd(MONTH, 1, date '2016-05-31'), timestampadd(MONTH, 5, date '2016-01-31'), timestampadd(MONTH, -1, date '2016-03-31') from depts; Calcite 1.12 Result: +--------+--------+--------+ | EXPR$0 | EXPR$1 | EXPR$2 | +--------+--------+--------+ | 2016-07-01 | 2016-07-01 | 2016-03-01 | +--------+--------+--------+ 3 rows selected (1.06 seconds) Calcite Master result: +--------+--------+--------+ | EXPR$0 | EXPR$1 | EXPR$2 | +--------+--------+--------+ | 2016-06-30 | 2016-06-30 | 2016-02-29 | +--------+--------+--------+ 3 rows selected (1.047 seconds) I think we need some hot fix for calcite 1.12. What do you think? Best, SunJincheng
        Hide
        julianhyde Julian Hyde added a comment -

        Thanks for updating the doc. Do you still intend to add DATEADD?

        Show
        julianhyde Julian Hyde added a comment - Thanks for updating the doc. Do you still intend to add DATEADD?
        Hide
        sunjincheng121 sunjincheng added a comment - - edited

        Hi Julian Hyde Thanks for the explanation. I found the SqlTimestampAddFunction in source code, And see the test case. You are right we supported to add TIMESTAMPADD and TIMESTAMPDIFF to reference.md.
        I have opened a PR. I appreciated if you can review the PR. https://github.com/apache/calcite/pull/466

        Thanks,
        SunJincheng

        Show
        sunjincheng121 sunjincheng added a comment - - edited Hi Julian Hyde Thanks for the explanation. I found the SqlTimestampAddFunction in source code, And see the test case. You are right we supported to add TIMESTAMPADD and TIMESTAMPDIFF to reference.md . I have opened a PR. I appreciated if you can review the PR. https://github.com/apache/calcite/pull/466 Thanks, SunJincheng
        Hide
        julianhyde Julian Hyde added a comment -

        That would be most welcome.

        We already have TIMESTAMPADD and TIMESTAMPDIFF. They are implemented by internally translating "TIMESTAMPADD(unit, n, t)" to "t + INTERVAL 'n' unit". Maybe you can use a similar approach.

        Be sure to add to reference.md. And also please add TIMESTAMPADD and TIMESTAMPDIFF, which didn't get documented when they were added in CALCITE-1124.

        Show
        julianhyde Julian Hyde added a comment - That would be most welcome. We already have TIMESTAMPADD and TIMESTAMPDIFF. They are implemented by internally translating "TIMESTAMPADD(unit, n, t)" to "t + INTERVAL 'n' unit". Maybe you can use a similar approach. Be sure to add to reference.md . And also please add TIMESTAMPADD and TIMESTAMPDIFF, which didn't get documented when they were added in CALCITE-1124 .
        Hide
        sunjincheng121 sunjincheng added a comment -

        Hi, Julian Hyde, If i not missing something, currently DATEADD is not supported by calcite. So I want add DATEADD as build-in scalar function. What do you think?

        Show
        sunjincheng121 sunjincheng added a comment - Hi, Julian Hyde , If i not missing something, currently DATEADD is not supported by calcite. So I want add DATEADD as build-in scalar function. What do you think?

          People

          • Assignee:
            sunjincheng121 sunjincheng
            Reporter:
            sunjincheng121 sunjincheng
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development