How do I format a date so that it respects timezones AND can be downloaded as a CSV? (Community)

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');;

}