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 <[email protected]> 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 <[email protected]> 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
>
