PostgreSQL - how to render date in different time zone?
datetime, postgresql, timezone
Solution
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;
Problem
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')`