Is it possible to get correct TZ abbreviation after converting column of type timestamptz
?
What I am after, sof example, is to display "2016-06-16 16:00:00-04" in "US/Pacific" timezone with target TZ abbrev. I can get this easily:
06/16/2016 01:00 pm
But I cannot get following:
06/16/2016 01:00 pm PDT
Once converted, timestamptz
becomes timestamp without timezone
and to_char
specifier 'TZ' becomes meaningless...
Here an example of what I have tried:
SELECT
scheduled_dt AS scheduled_dt
, to_char(scheduled_dt, 'TZ') AS scheduled_dt_orig_tz
-- correct value, but missing TZ
, timezone('US/Pacific', scheduled_dt) AS schedules_dt_converted_tz
, to_char(timezone('US/Pacific', scheduled_dt), 'MM/DD/YYYY HH:MI pm') AS scheduled_dt_converted_tz_localized
-- after conversion TZ is lost
, to_char(timezone('US/Pacific', scheduled_dt), 'MM/DD/YYYY HH:MI pm TZ') AS scheduled_dt_converted_tz_localized_missing_tz
-- with cast, wrong TZ is displayed
, to_char(timezone('US/Pacific', scheduled_dt)::timestamptz, 'MM/DD/YYYY HH:MI pm TZ') AS scheduled_dt_converted_tz_localized_wrong_tz
FROM orders
LIMIT 5
and sample output:
"scheduled_dt","scheduled_dt_orig_tz","schedules_dt_converted_tz","scheduled_dt_converted_tz_localized","scheduled_dt_converted_tz_localized_missing_tz","scheduled_dt_converted_tz_localized_wrong_tz"
"2016-06-16 16:00:00-04","EDT","2016-06-16 13:00:00","06/16/2016 01:00 pm","06/16/2016 01:00 pm ","06/16/2016 01:00 pm EDT"
"2014-07-15 08:00:00-04","EDT","2014-07-15 05:00:00","07/15/2014 05:00 am","07/15/2014 05:00 am ","07/15/2014 05:00 am EDT"
"2012-11-27 10:15:00-05","EST","2012-11-27 07:15:00","11/27/2012 07:15 am","11/27/2012 07:15 am ","11/27/2012 07:15 am EST"
"2011-03-02 08:30:00-05","EST","2011-03-02 05:30:00","03/02/2011 05:30 am","03/02/2011 05:30 am ","03/02/2011 05:30 am EST"
"2016-02-12 14:23:09-05","EST","2016-02-12 11:23:09","02/12/2016 11:23 am","02/12/2016 11:23 am ","02/12/2016 11:23 am EST"
No, because timestamptz
(aka timestamp with time zone
) isn't really a timestamp with a time zone, in terms of what's stored... you provide a timestamp and a time zone, and internally it's just stored as a UTC value.
It sounds like basically you should be storing the target time zone in a separate field along with the timestamp.
See more on this question at Stackoverflow