[jira] [Commented] (CALCITE-3345) Implement time_bucket function

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3345?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16929709#comment-16929709 ] 

Julian Hyde commented on CALCITE-3345:
--------------------------------------

Have you considered [HOP_START|https://calcite.apache.org/docs/reference.html#grouped-window-functions]? We already have it, it deals with ends of windows, and it uses standard interval values rather than integers or strings. 

> Implement time_bucket function
> ------------------------------
>
>                 Key: CALCITE-3345
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3345
>             Project: Calcite
>          Issue Type: New Feature
>            Reporter: Julian Feinauer
>            Priority: Major
>
> See here for information on the `time_bucket` function: https://docs.timescale.com/latest/api#time_bucket
> This is a more powerful version of the standard PostgreSQL date_trunc function. It allows for arbitrary time intervals instead of the second, minute, hour, etc. provided by date_trunc. The return value is the bucket's start time.
> This would especially help with time averaging but keeps everything SQL compliant. E.g. queries like 
> Example query from (https://www.timescale.com/):
> {code:sql}
> SELECT  time_bucket('10  seconds',  time)  AS  ten_second,
>     machine_id,  avg(temperature)  AS  "avgT",
>     min(temperature)  AS  "minT",  max(temperature)  AS  "maxT",
>     last(temperature,  time)  AS  "lastT"
> FROM  measurements
> WHERE  machine_id  =  'C931baF7'
>     AND  time  >  now()  -  interval  '150s'
> GROUP  BY  ten_second
> ORDER  BY  ten_second  DESC;
> {code}



--
This message was sent by Atlassian Jira
(v8.3.2#803003)