Search code examples
datetimeaggregateapache-drill

Apache Drill: Group by week

I tried to group my daily data by week (given a reference date) to generate a smaller panel data set.

I used postgres before and there it was quite easy:

CREATE TABLE videos_weekly AS SELECT channel_id,
CEIL(DATE_PART('day', observation_date - '2016-02-10')/7) AS week
FROM videos GROUP BY channel_id, week;

But it seems like it is not possible to subtract a timestamp with a date string in Drill. I found the AGE function, which returns an interval between two dates, but how to convert this into an integer (number of days or weeks)?

Solution

  • DATE_SUB may help you here. Following is an example:

    SELECT extract(day from date_sub('2016-11-13', cast('2015-01-01' as timestamp)))/7 FROM (VALUES(1));

    This will return number of weeks between 2015-01-01 and 2016-11-13.

    Click here for documentation