[
https://issues.apache.org/jira/browse/CALCITE-5155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17602569#comment-17602569
]
Thomas D'Silva commented on CALCITE-5155:
-----------------------------------------
I think defining custom time units would be useful. For eg, we have a use case
where we need to group data into 5 minute windows, so we run a query of the
form.
{code:java}
SELECT TIMESTAMPADD(MINUTE,(TIMESTAMPDIFF(MINUTE, TIMESTAMP'1970-01-01
00:00:00', EVENT_DATE)/5)*5, TIMESTAMP'1970-01-01 00:00:00') as EVENT_WINDOW,
COUNT(*) as COUNT_RECORDS from TABLE_NAME
{code}
This could be simplified to use FLOOR() with a user defined time unit.
{quote}Should we support time units that do not evenly divide the next largest
time unit?
{quote}
I think this can be supported for eg for {{"10 HOUR"}} {{parent()}} would only
have {{[(10, HOUR)]}} so if there is a materialized view on {{FLOOR(d TO '10
HOUR')}} it would not be used for a query that has {{FLOOR(d TO 'DAY').}}
Re 3, did you mean
{code:java}
FLOOR(FLOOR(d TO u) TO u) = FLOOR(d TO u)
CEIL(CEIL(d TO u) TO u)) = CEIL(d TO u)
{code}
Re 6, for CALCITE-5240 I will change {{getFloorMod}} to use the {{multiplier}}
field of {{startUnit}} of the input {{{}TimeUnitRange{}}}. For a
{{UserDefinedTimeUnit}} we could just derive the {{TimeUnit}} from
{{{}BUILT_IN_TIME_UNIT_MAP{}}}.
> Extensible time units
> ---------------------
>
> Key: CALCITE-5155
> URL: https://issues.apache.org/jira/browse/CALCITE-5155
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> 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}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
