12 hour time format on time dimension group

[Jack_P]

I have a date string in a postgres db

"2021-08-09 18:40:54.000+0000"

and am converting it to a dimension group with the following

{

    type: time
    datatype: timestamp   

sql: TO_TIMESTAMP(replace(${TABLE}."DateField", 'T', ' '), 'YYYY-MM-DD HH24:MI:SS.0000');;
}

If i change the HH24 to a 12 it kicks me out saying that 18 is out of range, and if i convert with 

to_char("CreatedDate"::timestamp, 'YYYY-DD-MM HH12:MI:SS PM')

It defaults back to the 24 hour time format despite putting the HH12 in. 

My field is stored in the database as a text field, and is in UTC 24 hour time format.

Is there a way to leverage the looker time dimension_group to set this to a 12 hour time format?

[olga1]

Hey Jack!

We can not change the format in the dimension group to be 12 hours instead of 24 hours. We have a feature request for it here: https://portal.feedback.us.pendo.io/app/#/case/41162 - feel free to upvote it. 

There are 2 things you can do as a workaround:

1. Create separate dimensions for those timeframes that you need, outside of dimension group,  and cast them using sql expressions to the format you need
2. Add formatting to the visualization settings using these expressions https://docs.looker.com/exploring-data/visualizing-query-results/time-formatting-for-charts  So it will be %I%p for 12 hour AM/PM format

Best,

Olga