'Druid Group by Day of Week and Hour of Day

I am trying to find how to group by day of week, and also hour of day in Druid. I am relying on Druid documentation here: https://druid.apache.org/docs/latest/querying/sql.html, and all the following functions are from this page.

I know that Druid has a function TIME_SHIFT(<timestamp_expr>, <period>, <step>, [<timezone>]) that can round down timestamp according to the period in ISO8601 standard, and then we can group by the rounded-down timestamp to achieve grouping by day time period we want.

  1. However, what if I want to group by day of week so that I can sum up all the sales income received on all Mondays, Tuesdays, Wednesdays,..., Sundays in a period of time? I know that there is a TIME_EXTRACT(<timestamp_expr>, [<unit>, [<timezone>]]) function, and I may be able to achieve what I want by using this function and = DOW (day of week) to transform each timestamp to a number (1 to 7) and then group by each of the extracted values. Is that correct?
  2. And what if I want to group by hour of day? I don't see a unit "hour of day" in this manual.

Thank you in advance!

Edit: It seems to be working; however, is there a way to make it print out Monday, Tuesday, Wednesday... instead of numbers from 1-7? I guess I can probably do some type of casting manually in the select block, but I am wondering if there is a better way? Thanks again!

sqldruid


Solution 1:[1]

Wouldn't TIME_EXTRACT(<timestamp_expr>, HOUR) do that?

For day names, I believe you can use:

TIME_FORMAT(<timestamp_expr>, [<pattern>, [<timezone>]])

For pattern it seems you can use 'EEEE' (for full text form of day name).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions