Обсуждение: Getting the name of the timezone, adjusted for daylight saving
Running 8.4.6 hosted on Linux, if I do this...
SELECT to_char('2011-03-01 12:00' AT TIME ZONE 'GMT0BST', 'HH24:MI TZ');
to_char
---------
12:00
(1 row)
..I don't get a timezone- I can live with that.
If I do this...
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)
..then I get the GMT time with a timezone 'GMT'- that's what I want.
But if I do this...
SELECT to_char(('2011-04-01 12:00' AT TIME ZONE 'GMT0BST')::TIMESTAMP
WITH TIME ZONE, 'HH24:MI TZ');
to_char
-----------
13:00 GMT
(1 row)
..then I get the time corrected for daylight saving- which is what I
want- but the timezone doesn't indicate that daylight saving has been
applied.
In the general case, how can I get TZ indicating whether daylight saving
is in effect? Alternatively, is there a flag I can retrieve indicating
that a timestamp has been corrected for DST so that I can select an
alternative name for display?
--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk
[Opinions above are the author's, not those of his employers or colleagues]
Mark Morgan Lloyd <markMLl.pgsql-general@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.
regards, tom lane
Tom Lane wrote:
> Mark Morgan Lloyd <markMLl.pgsql-general@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?
--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk
[Opinions above are the author's, not those of his employers or colleagues]
On 01/26/2011 09:00 AM, Mark Morgan Lloyd wrote:
> Tom Lane wrote:
>> Mark Morgan Lloyd <markMLl.pgsql-general@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
I hit send a bit quickly. To expand, no the result of to_char is not
always UTC, it is in whatever timezone you select (or the server-default
if the client doesn't specify).
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.
For example, I'm in the US Pacific Time Zone (PST8PDT) but I might want
to input an Eastern Time. Here's the result:
steve=# SELECT to_char(('2011-03-01 12:00' AT TIME ZONE
'EST5EDT')::TIMESTAMP WITH TIME ZONE, 'HH24:MI TZ');
to_char
-----------
15:00 PST
(1 row)
steve=# SELECT to_char(('2011-06-01 12:00' AT TIME ZONE
'EST5EDT')::TIMESTAMP WITH TIME ZONE, 'HH24:MI TZ');
to_char
-----------
15:00 PDT
(1 row)
Cheers,
Steve
On 01/26/2011 09:00 AM, Mark Morgan Lloyd wrote:
> Tom Lane wrote:
>> Mark Morgan Lloyd <markMLl.pgsql-general@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
Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.uk> writes:
> 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.
Consider setting timezone to what the client asks for.
> 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).
No, the result is computed on the assumption that we're in the zone
specified by the timezone setting. This might be hard to wrap your head
around if you only test in GMT or GMT0BST zones --- try something where
the offset is never zero to get a clearer picture of what's going on.
regards, tom lane
Steve Crawford <scrawford@pinpointresearch.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
Tom Lane wrote:
> Steve Crawford <scrawford@pinpointresearch.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]