Re: Getting the name of the timezone, adjusted for daylight saving

From: Mark Morgan Lloyd <markMLl(dot)pgsql-general(at)telemetry(dot)co(dot)uk>
To: pgsql-general(at)PostgreSQL(dot)org
Subject: Re: Getting the name of the timezone, adjusted for daylight saving
Date: 2011-01-27 10:03:57
Message-ID: ihrfuf$8or$1@pye-srv-01.telemetry.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Tom Lane wrote:
> 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.

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]

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Thom Brown 2011-01-27 10:08:31 Re: Queyring for columns which are exist in table.
Previous Message Santosh Bhujbal (sabhujba) 2011-01-27 09:53:14 Re: Queyring for columns which are exist in table.