PostgreSQL - how to render date in different time zone?

Question

My server is in Central Time. I would like to render timestamps using Eastern time.

For instance, I would like to render 2012-05-29 15:00:00 as 2012-05-29 16:00:00 EDT.

How can I achieve it?

to_char('2012-05-29 15:00:00'::timestamptz at time zone 'EST5EDT', 'YYYY-MM-DD HH24:MI:SS TZ') gives 2012-05-29 16:00:00 (no zone).

to_char('2012-05-29 15:00:00'::timestamp at time zone 'EST5EDT', 'YYYY-MM-DD HH24:MI:SS TZ') gives 2012-05-29 14:00:00 CDT (wrong).

This one works, but it's so ridiculously complicated there must be an easier way: replace(replace(to_char(('2012-05-29 15:00:00'::timestamptz at time zone 'EST5EDT')::timestamptz, 'YYYY-MM-DD HH24:MI:SS TZ'), 'CST', 'EST'), 'CDT', 'EDT')

Answer 1

The key is to switch the local timezone to the desired display timezone, for the duration of the transaction:

begin; set local timezone to 'EST5EDT'; select to_char('2012-05-29 15:00:00'::timestamp at time zone 'CDT',   'YYYY-MM-DD HH24:MI:SS TZ'); end; 

The result is:

2012-05-29 16:00:00 EDT

Note that with set [local] timezone it is required to use full time zone names instead of abbreviations (for instance, CST would not work). Look up in the pg_timezone_names view for valid choices.

To use that method in a context similar to a to_char() call, I believe this function does the job:

CREATE FUNCTION display_in_other_tz(       in_t timestamptz,       in_tzname text,       in_fmt text) RETURNS text AS $$ DECLARE  v text;  save_tz text; BEGIN   SHOW timezone into save_tz;   EXECUTE 'SET local timezone to ' || quote_literal(in_tzname);   SELECT to_char(in_t, in_fmt) INTO v;   EXECUTE 'SET local timezone to ' || quote_literal(save_tz);   RETURN v; END; $$ language plpgsql;