Re: Getting the name of the timezone, adjusted for daylight saving

On 01/26/2011 09:00 AM, Mark Morgan Lloyd wrote:
> Tom Lane wrote:
>> Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.uk> writes:
>>> SELECT to_char(('2011-03-01 12:00' AT TIME ZONE
>>> 'GMT0BST')::TIMESTAMP WITH TIME ZONE, 'HH24:MI TZ');
>>>    to_char
>>> -----------
>>>   12:00 GMT
>>> (1 row)
>>
>> You haven't said exactly what you were hoping to accomplish, but I
>> suspect the point here is to format a time according to some other zone
>> than the prevailing TimeZone setting.  You basically can't do that, at
>> least not with to_char and the timestamptz data type --- the information
>> just isn't there.  Consider creating a little plpgsql function that
>> temporarily changes the timezone setting and then calls to_char.
>
> Thanks Tom. Timestamps are going into the database which are
> implicitly UTC, and I was looking for a way to convert them when
> displayed to the local timezone (the client gets this from a
> configuration file and puts it in the query) and also to present the
> timezone name.
>
> So I think that what you're saying is that the result from to_char()
> will always be UTC

I hit send a bit quickly. To expand, no the result of to_char is not
always UTC, it is in whatever timezone you select (or the server-default
if the client doesn't specify).

I think you may have confused yourself by the order of operations. This:
('2011-03-01 12:00' AT TIME ZONE 'GMT0BST')::TIMESTAMP WITH TIME ZONE
created a timestamp from some text and you specified the time-zone to be
used in creating that value (stored internally in UTC). This was passed
to "to_char" which displayed the calculated the appropriate display of
that value in whatever time-zone the client was using.

For example, I'm in the US Pacific Time Zone (PST8PDT) but I might want
to input an Eastern Time. Here's the result:
steve=# SELECT to_char(('2011-03-01 12:00' AT TIME ZONE
'EST5EDT')::TIMESTAMP WITH TIME ZONE, 'HH24:MI TZ');
   to_char
-----------
  15:00 PST
(1 row)

steve=# SELECT to_char(('2011-06-01 12:00' AT TIME ZONE
'EST5EDT')::TIMESTAMP WITH TIME ZONE, 'HH24:MI TZ');
   to_char
-----------
  15:00 PDT
(1 row)

Cheers,
Steve