calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <julianh...@gmail.com>
Subject Re: How to group time by 15 min intervals
Date Mon, 27 Apr 2015 18:40:22 GMT
The answer on SO for MySQL is this*:

SELECT   ROUND(UNIX_TIMESTAMP(ts)/(15 * 60)) AS timekey
FROM     table
GROUP BY timekey;
The nearest equivalent in Calcite is this:

SELECT   cast(("hire_date" - timestamp '1970-01-01 00:00:00') minute as integer) / 15 AS timekey
FROM     table
GROUP BY cast(("hire_date" - timestamp '1970-01-01 00:00:00') minute as integer) / 15;

But doing date arithmetic in queries is an anti-pattern in ROLAP systems such as Kylin. A
time dimension table is the preferred solution. I suggest that you add minute to your time
dimension table (values 0 .. 59). You could also consider adding a quarter_hour column (values
0 .. 3). Then to find events in the same 15 minute interval you could group by date_id, hour,
quarter_hour.

Julian

* I renamed the column from “timestamp” to “ts” because TIMESTAMP is a reserved keyword.

On Apr 27, 2015, at 6:13 AM, Ethan Wang <castives@gmail.com> wrote:

> Looks like to me based on the stuff calcite has, you may create a calendar table first:
> 
> tbl_calendar
> id  min   max 
> 1    0	15
> 2   16	30
> 3   31	45
> 4   46	60
> 
> 
> select * from 
> main,
> tbl_calendar
> where
> main.timestamp >= tbl_calendar.min
> and  main.timestamp <  tbl_calendar.max
> group by
> tbl_calendar.id
> 
> 
> 
> 
> 
>> On Apr 27, 2015, at 5:19 AM, Li Yang <liyang@apache.org> wrote:
>> 
>> Hi
>> 
>> Given a timestamp column, I want to group by every 15 minutes like [1].
>> How can this be best done with calcite?
>> 
>> Looked at the reference, but answer is not obvious.
>> 
>> 
>> Thanks
>> Yang
>> 
>> 
>> [1]
>> http://stackoverflow.com/questions/2793994/group-mysql-query-by-15-min-intervals
>> [2] https://github.com/apache/incubator-calcite/blob/master/doc/REFERENCE.md
> 


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message