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!
