From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
Cc: | Mark Morgan Lloyd <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)uk>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Getting the name of the timezone, adjusted for daylight saving |
Date: | 2011-01-26 20:31:11 |
Message-ID: | 24818.1296073871@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Steve Crawford <scrawford(at)pinpointresearch(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Lawrence Cohan | 2011-01-26 21:29:22 | Adding ddl audit trigger |
Previous Message | Alban Hertroys | 2011-01-26 20:25:46 | Re: check on foreign table? |