On 01/26/2011 09:00 AM, Mark Morgan Lloyd wrote:
Tom Lane wrote:
Mark Morgan Lloyd <markmll.pgsql-gene...@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, possibly corrected for daylight saving (i.e. GMT
or BST respectively). I can live with that, the app is for local use
and we're not that far from Greenwich :-)
Now if I'm reading the documentation correctly I can refer to
pg_timezone_names and use is_dst to determine whether a particular
timezone (e.g. GMT) has a daylight saving correction applied at the
current date. But from there is there any cross-reference so that if
DST is active I can change the displayed TZ name to BST?
I think you want to set the client timezone explicitly:
steve=# set timezone to GMT0BST;
SET
steve=# select to_char('2011-07-15'::timestamptz,'HH24:MI TZ');
to_char
-----------
00:00 BST
(1 row)
steve=# select to_char('2011-01-15'::timestamptz,'HH24:MI TZ');
to_char
-----------
00:00 GMT
(1 row)
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
