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

Custom time frames

    XMLWordPrintableJSON

Details

    Description

      Allow a type system to define its own time units and how they are rolled up.

      Currently, time units are used in the EXTRACT, FLOOR, TRUNC functions, and include YEAR, QUARTER, MONTH, HOUR, MINUTE, NANOSECOND. For example FLOOR(t TO HOUR) is valid.

      A type system would be allowed to define extra time units. Once a time unit is defined the EXTRACT, FLOOR and TRUNC functions should just work.

      The definition of might consist of a base unit and multiplier. So MINUTE15 would be based on MINUTE with a multiplier of 15. 

      Various rules know that you can roll up FLOOR(t TO DAY) to FLOOR(t TO MONTH) but you cannot roll FLOOR(t TO WEEK) to FLOOR(t TO MONTH). When you define a new time unit, the type system can deduce that full set of time units that it can roll up to, and which can roll up to it.

      Should we support time units that do not evenly divide the next largest time unit? For example the number of seconds since the top of the hour modulo 7. 60 and 3,600 are not a multiples of 7, so SecondOfHourMod7 would be different from SecondOfMinuteMod7.

      Attachments

        Issue Links

          blocks

          Sub-task - The sub-task of the issue CALCITE-5360 Implement TIMESTAMP_ADD function (compatible with BigQuery)

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Improvement - An improvement or enhancement to an existing feature or task. CALCITE-5222 Support more EXTRACT field values (or allow arbitrary in parse?)

          • Major - Major loss of function.
          • Closed
          is related to

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-5369 In Avatica DateTimeUtils, add support for FLOOR and CEIL to ISOYEAR

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-5290 Add DATE_TRUNC function consistent with Postgres

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-5363 Move DATEADD, DATEDIFF, DATE_PART from Babel to Core parser

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-5364 In materialized view substitution, use custom time frames

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-5143 Allow custom time unit abbreviations in FLOOR, CEIL, EXTRACT, DATE_PART, DATEADD, DATEDIFF and similar functions

          • Major - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. CALCITE-5240 Enhance MaterializedViewRule so that it applies to rollup view for queries that contain a predicate on the rollup column

          • Major - Major loss of function.
          • Open
          links to

          Web Link GitHub Pull Request #2960

          Activity

            People

              julianhyde Julian Hyde
              julianhyde Julian Hyde
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 2h 10m
                  2h 10m