View the original community article here
Last tested: Jan 21, 2019
It's convenient to format a date with liquid, but HTML formatting is not respected in CSV downloads.
So you might use SQL's to_char
on a timestamp, but then your string dimension will not listen to user-specific timezones.
To get the best of both worlds, you need to reference the time
timeframe (which is itself a string) to bring in the timezone conversion, then convert it BACK to a timestamp so you can format it in the way you like.
The following example applies redshift formatting for AM/PM dates. You can change the last format string to anything you want.
dimension: formatted_time {
# This converts the timestamp to AM/PM time while respecting timezones AND allowing for CSV downloads.
# First we must reference the `time` timeframe (NOT `raw` or ${TABLE}.date) so that timezone conversion is respected.
# Since `time` is already a Looker-formatted string, we must convert it back to a timestamp so we can do our own formatting.
# Finally we apply our own formatting to the timestamp.
label: "Formatted AM/PM Time"
group_label: "created"
type: string
sql: to_char(TO_TIMESTAMP(${created_time},'YYYY-MM-DD HH:MI:SS'),'YYYY-MM-DD HH:MI:SS AM');;
}
This content is subject to limited support.