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.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
