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!
@blue1 - have you tried the below?
to_char(${finished_hour_of_day}, 'HH12 AM')
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.