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!

CodePudding user response：

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).