On 01/26/2011 09:00 AM, Mark Morgan Lloyd wrote:
Tom Lane wrote:Mark Morgan Lloyd <markMLl.pgsql-general@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
