Starting working day at custom hours

Get Help
1

Hi

I'm using the GROUPED BY Hour of day to show the business day performance.
Our Business day lasts 18 hours, starting from 11AM and ending at 4AM. I really need to change the start hour in the result to 11AM (and end hour at 4AM) in order to have the graph related to the real working day hours and not to have 0 columns for the hours in-between.

That's an example of a visualization now:

Screenshot (51)
Screenshot (51)2031×1018 58.6 KB

Can Metabase do that? Or, can anyone write that in SQL?
That's a collage of the wished-graph (just to be clear):

photo1668177127
photo16681771271280×780 37.6 KB

Hope someone would help me. Thanks.

2

Hi @Elis_15
You would have to do that in SQL and change the axis to Ordinal, while also ordering with a timeshift.
There's currently no simple way of handling this in Metabase by using the GUI.

3

Can you roughtly write for me the sql? ...otherwise, can you explain a little bit more how to do so?

Thank you so much!

4

@Elis_15 I have no idea which database type you are using. Post the SQL of your GUI question.
https://www.metabase.com/docs/latest/questions/query-builder/introduction#viewing-the-sql-that-powers-your-question

5

That's my sql of the previous view:

SELECT CAST(extract(hour from "public"."Drinks"."createdAt") AS integer) AS "createdAt", count() AS "count"*
FROM "public"."Drinks"
LEFT JOIN "public"."IngredientQuantity" "IngredientQuantity" ON "public"."Drinks"."id" = "IngredientQuantity"."drinkId" LEFT JOIN "public"."Ingredients" "Ingredients - IngredientId" ON "IngredientQuantity"."ingredientId" = "Ingredients - IngredientId"."id" LEFT JOIN "public"."AppOrders" "AppOrders__via__appOrderId" ON "public"."Drinks"."appOrderId" = "AppOrders__via__appOrderId"."id"
WHERE ("public"."Drinks"."type" = 'customer'
AND "public"."Drinks"."alcohol" = TRUE AND "public"."Drinks"."barId" = 1 AND "public"."Drinks"."status" = 'delivered' AND "AppOrders__via__appOrderId"."createdAt" >= timestamp with time zone '2022-09-01 00:00:00.000Z' AND "AppOrders__via__appOrderId"."createdAt" < timestamp with time zone '2022-10-01 00:00:00.000Z')
GROUP BY CAST(extract(hour from "public"."Drinks"."createdAt") AS integer)
ORDER BY CAST(extract(hour from "public"."Drinks"."createdAt") AS integer) ASC

6

@Elis_15 Change the order clause to something like this:

ORDER BY CAST(extract(hour from "public"."Drinks"."createdAt") AS integer)+(CASE WHEN CAST(extract(hour from "public"."Drinks"."createdAt") AS integer)<11 THEN 23 ELSE 0 END) ASC

And then change the visualization Settings > Axes > X-axis scale = Ordinal

7

It works that way!! Thank you so much