32

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

Improve this question
6
  • How is the server supposed to know which time zone abbreviation you want to display? It only knows the time zone offset Commented May 29, 2012 at 11:31
  • 1
    Time zone information is not black magic. There is no technical reason that would make it impossible (or even hard) to say that 5/29 is DST in Eastern Time. In fact, PG knows it all - to_char(x, 'TZ') differentiates CST from CDT correctly, and at time zone EST5EDT respects DST as well. The only bit I'm missing is elegant rendering using this information. Commented May 29, 2012 at 11:38
  • 1
    The problem here is in the bizarre choice of the Postgres devs that timestamp with time zone at _zone_ should convert the time zones, but return timestamp without time zone. Commented May 29, 2012 at 11:48
  • 2
    @lanzz There are many bizarre places around converting timestamp <-> timestamptz and using at time zone, but is there really no built-in way to render different time zone, like fmt_date(x, 'YYYY-MM-DD HH24:MI:SS TZ', 'EST5EDT')? Commented May 29, 2012 at 12:31
  • I've pondered that same issue at some point in the past, and as far as I can remember, couldn't find a sane solution. Probably best you can do is manage timezones and formatting on application side instead of in the database, though this is not really an answer to your question. Commented May 29, 2012 at 12:36

2 Answers 2

Reset to default
45

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
Ok I can see that I misunderstood that part of the problem. Changing my answer accordingly.
+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.
this makes me appreciate how concise is the shell, e.g: TZ=Australia/Perth date
4

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.

Improve this answer

2 Comments

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.
@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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.