Tom Lane wrote:
Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> writes:
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.
One other point here is that I believe that given an undecorated literal
like that, the parser will prefer to assume it is timestamp *with* time
zone if the alternatives are with or without. So what you actually had
here was
1. Literal constant '2011-03-01 12:00' is assumed to be written in
whatever your timezone is; then it's rotated into UTC time.
2. AT TIME ZONE converts this to timestamp *without* time zone, rotating
into the specified 'GMT0BST' zone.
3. The cast converts back to timestamp *with* time zone, again assuming
that the given unlabeled timestamp is in the timezone zone and rotating
that to UTC.
4. If you feed the result to to_char, it's going to rotate the UTC value
back into the timezone zone and then format that.
Somewhere along here you have a complete mess. It might accidentally
fail to fail if tested with a timezone setting of GMT or GMT0BST, but
otherwise it's an extremely expensive way of getting the wrong answer.
The right way to specify a time that's known to be measured in a
particular timezone is something like
'2011-03-01 12:00 GMT0BST' :: timestamptz
or if you prefer you can do
'2011-03-01 12:00'::timestamp AT TIME ZONE 'GMT0BST'
Either of these will produce the same timestamptz value, but note the
explicit casts are important.
If you've got a timestamptz value, and you want to display it in a
particular zone and include the zone identity in the output, I don't
think there is any way that is more practical than changing the timezone
setting to that zone. After that you can either just display the value,
or use to_char if you don't like the default formatting. The AT TIME
ZONE construct is *not* very helpful for this because it won't show
anything about the zone. I suppose you could write
(timestamptz_value AT TIME ZONE 'GMT0BST') || ' GMT0BST'
but that seems like a crock.
Thanks Steve and Tom. So to sum up this particular subthread:
i) AT TIME ZONE primarily influences input, not output.
ii) If I want to influence output, then I need to (temporarily) change
the session's timezone setting.
But out of curiosity will (ii) also mess up extract(epoch ...), or will
that consistently return the number of UTC seconds rather than adjusting
for the local timezone?
If it does, is there a "right" way of restricting the scope of a
timezone change to a single function?
--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk
[Opinions above are the author's, not those of his employers or colleagues]
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
