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

Solution 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;

Solution 2

In fact, PG knows it all - to_char(x, 'TZ') differentiates CST from CDT correctly, and at time zone EST5EDT respects DST as well.

When dealing with a timestamp Postgres knows:

• The setting of the GUC timezone.
• The data type.
• The value, which is the same count of seconds since '1970-1-1 0:0 UTC' for timestamp and timestamptz. (Or, to be precise: UT1.)
• Details about other time zones in your date/time configuration files

When interpreting input, Postgres uses information about the provided time zone. When rendering a timestamp value, Postgres uses the current timezone setting, but time zone offset, abbreviation or name are only used to compute the correct value on input. They are not saved. It is impossible to extract that information later. More details in this related answer:

Your "correct" example is almost correct. TZ of to_char() returns 'CDT' for timestamps that fall in the daylight saving periods of Central Time and 'CST' else. Eastern Time (EST /EDT) switches daylight saving hours at the same local time - I quote Wikipedia:

The time is adjusted at 2:00 AM local time.

The two time zones are out of sync during two hours per year. Of course, this can never affect a timestamp at 15:00 or 16:00, only around 02:00.

A fully correct solution - much like what @Daniel already posted, slightly simplified:

BEGIN;
SET LOCAL timezone to 'EST5EDT';
SELECT to_char('2012-05-29 15:00 CST6CDT'::timestamptz
             , 'YYYY-MM-DD HH24:MI:SS TZ')
RESET timezone;  -- only if more commands follow in this transactions
END;

The effects of SET LOCAL last only till the end of the current transaction.

The manual about SET LOCAL.

Comments

[Konrad Garus]

This is not what I was looking for. I asked about date rendering, not conversion. I know how to convert to timestamp, what I'm looking for is a way to render a varchar with different time zone name in a general case.

[Erwin Brandstetter]

Your first answer is correct. As I understand the question, the OP wants the system to output 'EDT' or 'EST' depending on the timestamp. Your later addition would fail in this respect.

[Erwin Brandstetter]

@KonradGarus: I didn't have more time earlier today. I think I understand more clearly what you are after now and also why your solution is almost, but not quite, correct.

[Daniel Vérité]

Ok I can see that I misunderstood that part of the problem. Changing my answer accordingly.

[Erwin Brandstetter]

+1 Very clean now. Your last version is also an improvement in that it resets the current timezone. I was assuming the default timezone. And I like the SHOW timezone INTO.

[Sam Watkins]

this makes me appreciate how concise is the shell, e.g: TZ=Australia/Perth date