LinkedIn
Twitter
Hello fellow Lookers,
I am facing an interesting challenge: I created a heatmap of production data and want to convert “Hour of Day” from 24-hour into the 12-hour time system.
You can see in the image above that the “Hour of Day” from dimension_group type: time is formatted as the 24-hour clock. I would like to see this in the 12-hour AM/PM format.
I tried creating a new dimension with custom HTML formatting options, but it does not work properly.
dimension: hour_of_day_formatted {
group_label: “Finished Date” label: “Finished Hour of Day (12)”
sql: ${TABLE}.FinishedDatetime ;;
html: {{ rendered_value | date: “%I %p” }};;
}
The formatting is correct, but since my input is every date value, I have a fanout of rows as you can see below.
I tried using Hours of Day as input to conversion, which fixes the fanout, but then the formatting no longer works.
dimension: finished_datetime_hour_of_day_formatted {
group_label: “Finished Date” label: “Finished Hour of Day (12)”
sql: ${finished_datetime_hour_of_day} ;;
html: {{ rendered_value | date: “%I %p” }};;
}
Ideally, this would be handled automatically based on the Locale or Timezone settings of the user.
Does anyone have any other ideas?
Documentation generally revolves around converting date-time to a different timezone, which Looker handles automatically, but sadly it does not handle formatting in the same way. I have tried changing my Locale or Timezone, but it does not have any effect on the formatting of time or dates.
Thank you for any and all comments!
Hi Lauren,
Thank you for your idea!
Unfortunately, to_char() does not work for me (I am on Microsoft Server 2016+), however, it did send me down the right path.
The challenge was to convert the input of “Hours per Day” (Integer) into DateTime so I could use FORMAT() to convert the clock.
FORMAT( CAST( CONVERT( CHAR(5), DATEADD(MINUTE, 60*${finished_hour_of_day}, 0), 108 ) AS DATETIME ), ‘hh:mm tt’ )
First, I convert the integer into time using CONVERT(), then I cast that Time as Datetime using CAST(), and finally, I reformat it to AM/PM using FORMAT().
Some problems still remain, for example, as you can see above, the sorting does not work properly. I can fix this by including the “Hour of Day” as a hidden dimension and sorting on that instead.
That brings my second issue, Looker no longer fills missing dates when I add the formatted hour dimension to the query.
Thanks again for your input, Lauren!
I really wish Looker would handle date formatting and hour formatting based on timezone or locale settings.
