Обсуждение: Messed up time zones

select abbrev,utc_offset,count(*) from pg_timezone_names
where abbrev='EST'
group by abbrev,utc_offset

There are 12 times zones with 'EST' code, offset = GMT+10. And there are
8 time zones with 'EST' code, offset= GMT+5 at the same time!

So how much it is supposed to be?

select now() at time zone 'UTC' - now() at time zone 'EST'

(Actually it returns +5:00 but what is the explanation?)

And how am I supposed to convert a date to Australian zone? This doesn't
work:

select now() at time zone 'Australia/ATC' -- time zone "Australia/ATC"
not recognized

Background: we have a site where multiple users are storing data in the
same database. All dates are stored in UTC, but they are allowed to give
their preferred time zone as a "user preference". So far so good. The
users saves the code of the time zone, and we convert all timestamps in
all queries with their preferred time zone. But we got some complaints,
and this is how I discovered the problem.

Actually, there are multiple duplications:


select abbrev,count(distinct utc_offset)
from pg_timezone_names
group by abbrev
having count(distinct utc_offset)>1
order by 2 desc


"CST";3
"CDT";2
"AST";2
"GST";2
"IST";2
"WST";2
"EST";2


How should I store the user's preferred time zone, and how am I supposed
to convert dates into that time zone?

Thanks,

    Laszlo

Re-sending this since I seem to have left out the list itself:

Isn't:
--
JC de Villa

Isn't:

select now() at time zone 'Australia/ATC' 

supposed to be:

select now() at time zone 'Australia/ACT'

I see now. The abbreviation is usually a time zone name. But to be correct, the time zone name should be used (and not the abbreviation).

And looking at the pg_timezone_names table for EST, there's only one entry for EST:

SELECT * from pg_timezone_names where name = 'EST';

 name | abbrev | utc_offset | is_dst 

------+--------+------------+--------

 EST  | EST    | -05:00:00  | f

Okay, but that is the "name", and not the "abbrev" field. So time zone abbreviations are not unique? Then probably it is my fault - I thought that they will be unique. It is still an interesting question, how others interpret these (non-unique) abbreviations? But I guess that is not related to PostgreSQL so I'm being offtopic here.


One last question. Am I right in that PostgreSQL does not handle leap seconds?

template1=> set time zone 'UTC';
template1=> select '2008-12-31 23:59:60'::timestamp;
      timestamp     
---------------------
 2009-01-01 00:00:00
(1 row)

And probably intervals are affected too:

template1=> set time zone 'UTC';
template1=> select '2008-12-31 00:00:00'::timestamp + '48 hours'::interval;
      timestamp     
---------------------
 2009-01-02 00:00:00
(1 row)

Should be '2009-01-01 23:59:59' instead.

Thanks,

   Laszlo

Well, per the docs at http://www.postgresql.org/docs/9.1/static/functions-datetime.html,  in parens under timezone:

On 2012-08-03 10:31, Laszlo Nagy wrote:
> select abbrev,utc_offset,count(*) from pg_timezone_names
> where abbrev='EST'
> group by abbrev,utc_offset
>
> There are 12 times zones with 'EST' code, offset = GMT+10. And there
> are 8 time zones with 'EST' code, offset= GMT+5 at the same time!
Sorry, I still have some questions.

template1=> set time zone 'UTC';
template1=> select to_char(('2011-10-30 00:00:00'::timestamp at time
zone 'UTC') at time zone 'Europe/Budapest', 'YYYY-MM-DD HH24:MI:SS TZ');
        to_char
----------------------
  2011-10-30 02:00:00
(1 row)

template1=> select to_char(('2011-10-30 01:00:00'::timestamp at time
zone 'UTC') at time zone 'Europe/Budapest', 'YYYY-MM-DD HH24:MI:SS TZ');
        to_char
----------------------
  2011-10-30 02:00:00
(1 row)


The time zone was not included in the output. I guess it is because the
last "at time zone" part converted the timestamptz into a timestamp.
Right now, these results don't just look the same. They are actually the
same values, which is obviously not what I want. They have been
converted from different UTC values, so they should be different. I
would like to see "2011-10-30 02:00:00+0600" and "2011-10-30
02:00:00+0500", or something similar.

So the question is: how do I convert a timestamptz value into a
different time zone, without changing its type? E.g. it should remain a
timestamptz, but have a (possibly) different value and a different time
zone assigned.

Thanks,

    Laszlo

Here is a better example that shows what I actually have in my database.
Suppose I have this table, with UTC timestamps in it:

template1=> create table test ( a  timestamptz not null primary key );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
template1=> insert into test values ('2011-10-30 00:00:00'::timestamp at
time zone 'UTC');
INSERT 0 1
template1=> insert into test values ('2011-10-30 01:00:00'::timestamp at
time zone 'UTC');
INSERT 0 1
template1=> set datestyle to "postgres, postgres";
SET
template1=> select * from test;
               a
------------------------------
  Sun Oct 30 00:00:00 2011 UTC
  Sun Oct 30 01:00:00 2011 UTC
(2 rows)


I would like to see the same values, just converted into a different
time zone. But still have timestamptz type!

So I try this:


template1=> select a at time zone 'Europe/Budapest' from test;
          timezone
--------------------------
  Sun Oct 30 02:00:00 2011
  Sun Oct 30 02:00:00 2011
(2 rows)

Which is not good, because the zone information was lost, and so I see
identical values, but they should be different.

Casting to timestamptz doesn't help either, because casting happens
after the time zone information was lost:

template1=> select (a at time zone 'Europe/Budapest')::timestamptz from
test;
            timezone
------------------------------
  Sun Oct 30 02:00:00 2011 UTC
  Sun Oct 30 02:00:00 2011 UTC
(2 rows)

template1=>

So how do I create a query that results in something like:

            a
------------------------------
  Sun Oct 30 02:00:00 2011 +0500
  Sun Oct 30 02:00:00 2011 +0600
(2 rows)

Laszlo Nagy <gandalf@shopzeus.com> writes:
> So how do I create a query that results in something like:

>             a
> ------------------------------
>   Sun Oct 30 02:00:00 2011 +0500
>   Sun Oct 30 02:00:00 2011 +0600
> (2 rows)

Set the "timezone" setting to the zone you have in mind, and then just
print the values.  The reason there's no manual way to do rotation
across zones is that there's no need for one because it's done
automatically during printout of a timestamptz value.

I suspect that you have not correctly internalized what timestamptz
values actually are.  Internally they are just time values specified in
UTC (or UT1 if you want to be picky).  On input, the value is rotated
from whatever zone is specified in the string (or implicitly specified
by "timezone") to UTC.  On output, the value is rotated from UTC to
whatever the current "timezone" setting is.

            regards, tom lane

On 2012-08-03 16:19, Tom Lane wrote:
> Laszlo Nagy <gandalf@shopzeus.com> writes:
>> So how do I create a query that results in something like:
>>              a
>> ------------------------------
>>    Sun Oct 30 02:00:00 2011 +0500
>>    Sun Oct 30 02:00:00 2011 +0600
>> (2 rows)
> Set the "timezone" setting to the zone you have in mind, and then just
> print the values.

majorforms=> set time zone 'Europe/Budapest';
SET
majorforms=> select * from test;
            a
------------------------
  2011-10-30 02:00:00+02
  2011-10-30 02:00:00+01
(2 rows)

majorforms=>

It works. Thank you!

So is it impossible to construct a query with columns that are different
time zones? I hope I'm not going to need that. :-)

>    The reason there's no manual way to do rotation
> across zones is that there's no need for one because it's done
> automatically during printout of a timestamptz value.
I can come up with an example when it would be needed. For example,
consider a company with two sites in different time zones. Let's say
that they want to store time stamps of online meetings. They need to
create a report that shows the starting time of the all meetings *in
both zones*. I see no way to do this in PostgreSQL. Of course, you can
always select the timestamps in UTC, and convert them into other time
zones with a program so it is not a big problem. And if we go that
route, then there is not much point in using the timestamptz type, since
we already have to convert the values with a program...

>
> I suspect that you have not correctly internalized what timestamptz
> values actually are.  Internally they are just time values specified in
> UTC (or UT1 if you want to be picky).  On input, the value is rotated
> from whatever zone is specified in the string (or implicitly specified
> by "timezone") to UTC.  On output, the value is rotated from UTC to
> whatever the current "timezone" setting is.
Oh I see. So actually they don't store the zone? I have seen that
timestamptz and timestamp both occupy 8 bytes, but I didn't understand
completely.

It also means that if I want to store the actual time zone (in what the
value was originally recorded), then I have to store the zone in a
separate field. Later I can convert back to the original time zone, but
only with an external program.

Fine with me. I'm happy with this, just I did not understand how it works.

Thanks,

    Laszlo

On 8/3/2012 11:23 AM, Laszlo Nagy wrote:
>> I suspect that you have not correctly internalized what timestamptz
>> values actually are.  Internally they are just time values specified in
>> UTC (or UT1 if you want to be picky).  On input, the value is rotated
>> from whatever zone is specified in the string (or implicitly specified
>> by "timezone") to UTC.  On output, the value is rotated from UTC to
>> whatever the current "timezone" setting is.
> Oh I see. So actually they don't store the zone? I have seen that timestamptz and timestamp both occupy 8 bytes, but
Ididn't understand completely. 
>
> It also means that if I want to store the actual time zone (in what the value was originally recorded), then I have
tostore the zone in a separate field. Later I can convert back to the original time zone, but only with an external
program.
>
> Fine with me. I'm happy with this, just I did not understand how it works.

You could store the zone in a separate field and then create a VIEW on the table that used a function to take both
valuesand return the timestamptz just as it was inserted. 

> You could store the zone in a separate field and then create a VIEW on
> the table that used a function to take both values and return the
> timestamptz just as it was inserted.
>
Well no, it is not possible.  A timestamptz value is interpreted as UTC,
regardless of your local timezone. A timestamp value is interpreted in
your local time zone. This is the main difference between them. You can
change *the interpretation* of these values with the "at time zone"
expression. But you cannot convert between time zones at all! Time zone
information is not stored in any way - it is a global setting.

I have intentionally chosen an example where the local time is changed
from summer time to winter time (e.g. local time suddenly "goes back"
one hour). It demonstrates that you cannot use "at time zone ...."
expression to convert a timestamptz into a desired time zone manually.

The only case when time zone conversion occurs is when you format the
timestamp/timestamptz value into a text. As Tom Lane pointed out, the
only correct way to convert a timestamptz/timestamp value into a desired
time zone is to use the "set time zone to ...." command. But that
command has a global effect, and it does not actually change the zone of
the stored value (because the time zone is not stored at all). It just
changes the formatting of those values, and as a result, you will get a
correct textual representation of the original timestamp value in the
desired time zone. But you will *never* be able to get a correct
timestamp value in a desired time zone. All you can get is text.

As far as I'm concerned, I'm going to set the system's clock to UTC,
store everything in timestamp field (in UTC),  and use a program to
convert fetched values before displaying them.

Regards,

    Laszlo

On 08/03/2012 08:23 AM, Laszlo Nagy wrote:
> ...
>
> It works. Thank you!
>
> So is it impossible to construct a query with columns that are
> different time zones? I hope I'm not going to need that. :-)
>

I'm not sure you have internalized the meaning of timestamptz. It helps
to instead think of it as a "point in time", i.e. the shuttle launched at...

select
now() at time zone 'UTC' as "UTC",
now() at time zone 'Asia/Urumqi' as "Urumqi",
now() at time zone 'Asia/Katmandu' as "Katmandu",
now() at time zone 'America/Martinique' as "Martinique",
now() at time zone 'America/Kralendijk' as "Kralendijk",
now() at time zone 'Africa/Algiers' as "Algiers",
now() at time zone 'Europe/Zurich' as "Zurich",
now() at time zone 'Australia/Brisbane' as "Brisbane",
now() at time zone 'Pacific/Galapagos' as "Galapagos"
;

-[ RECORD 1 ]--------------------------
UTC        | 2012-08-03 15:54:49.645586
Urumqi     | 2012-08-03 23:54:49.645586
Katmandu   | 2012-08-03 21:39:49.645586
Martinique | 2012-08-03 11:54:49.645586
Kralendijk | 2012-08-03 11:54:49.645586
Algiers    | 2012-08-03 16:54:49.645586
Zurich     | 2012-08-03 17:54:49.645586
Brisbane   | 2012-08-04 01:54:49.645586
Galapagos  | 2012-08-03 09:54:49.645586

All the above are the exact same point in time merely stated as relevant
to each location. Note that given a timestamp with time zone and a zone,
PostgreSQL returns a timestamp without time zone (you know the zone
since you specified it). Conversely, given a local time (timestamp with
out time zone) and a known location you can get the point in time
(timestamptz):

select
'2012-08-03 15:54:49.645586 UTC'::timestamptz,
'2012-08-03 15:54:49.645586 Asia/Urumqi'::timestamptz,
'2012-08-03 15:54:49.645586 Asia/Katmandu'::timestamptz,
'2012-08-03 15:54:49.645586 America/Martinique'::timestamptz,
'2012-08-03 15:54:49.645586 America/Kralendijk'::timestamptz,
'2012-08-03 15:54:49.645586 Africa/Algiers'::timestamptz,
'2012-08-03 15:54:49.645586 Europe/Zurich'::timestamptz,
'2012-08-03 15:54:49.645586 Australia/Brisbane'::timestamptz,
'2012-08-03 15:54:49.645586 Pacific/Galapagos'::timestamptz
;

-[ RECORD 1 ]------------------------------
timestamptz | 2012-08-03 08:54:49.645586-07
timestamptz | 2012-08-03 00:54:49.645586-07
timestamptz | 2012-08-03 03:09:49.645586-07
timestamptz | 2012-08-03 12:54:49.645586-07
timestamptz | 2012-08-03 12:54:49.645586-07
timestamptz | 2012-08-03 07:54:49.645586-07
timestamptz | 2012-08-03 06:54:49.645586-07
timestamptz | 2012-08-02 22:54:49.645586-07
timestamptz | 2012-08-03 14:54:49.645586-07

I'm currently in Pacific Daylight Time hence the -07. But note that you
can specify an offset (-07) that is not the same as
'America/Los_Angeles'. -07 is an offset, 'America/Los_Angeles' is a time
zone and deals appropriately with Daylight Saving Time and the various
changes thereto through history.

Should it be necessary, you could save time zone information in a
separate column. Note that you can specify time zone as a characteristic
of a user if your database handles users across multiple zones (alter
user steve set timezone to 'America/Los_Angeles';)

It takes a bit of reading and experimenting to understand the subtleties
of date/time handling but it's time well spent.

Cheers,
Steve

Laszlo Nagy <gandalf@shopzeus.com> writes:
> I have intentionally chosen an example where the local time is changed
> from summer time to winter time (e.g. local time suddenly "goes back"
> one hour). It demonstrates that you cannot use "at time zone ...."
> expression to convert a timestamptz into a desired time zone manually.

Um, yes you can.  The trick is to use a timezone name, not an
abbreviation, in the AT TIME ZONE construct (for instance,
'Europe/Budapest' not just 'CET').  That will do the rotation
in a DST-aware fashion.

> As far as I'm concerned, I'm going to set the system's clock to UTC,
> store everything in timestamp field (in UTC),  and use a program to
> convert fetched values before displaying them.

[ shrug... ]  If you really insist on re-inventing that wheel, go
ahead, but it sounds to me like you'll just be introducing additional
points of failure.

            regards, tom lane

2012.08.03. 18:38 keltezéssel, Tom Lane írta:
> Laszlo Nagy <gandalf@shopzeus.com> writes:
>> I have intentionally chosen an example where the local time is changed
>> from summer time to winter time (e.g. local time suddenly "goes back"
>> one hour). It demonstrates that you cannot use "at time zone ...."
>> expression to convert a timestamptz into a desired time zone manually.
> Um, yes you can.  The trick is to use a timezone name, not an
> abbreviation, in the AT TIME ZONE construct (for instance,
> 'Europe/Budapest' not just 'CET').  That will do the rotation
> in a DST-aware fashion.
And loose information at the same time. Because after the conversion,
you won't be able to tell if it is a summer or a winter time. So yes,
you are right. You can do that kind of conversion, but then sometimes
you won't know when it was, or what it means. This problem could be
solved by storing the UTC offset together with the time zone, internally
in PostgreSQL.

Maybe, if that is not a problem for the user, he can use "at time zone"
for converting between time zones. Personally, I will stick with UTC and
use a program to convert values, because I would like to know when it
was. :-)

 > [ shrug... ] If you really insist on re-inventing that wheel, go
ahead, but it sounds to me like you'll just be introducing additional
points of failure. regards, tom lane

I just checked some programming languages (Python, C#), and the same
problem exists there. All of them say that "when the time is ambiguous,
then it is assumed to be in standard time". So the representation is
ambiguous in various programming languages too. You are right - it would
be reinventing the wheel.

Although I don't like the fact that we are using an ambiguous system for
measuring time, after all the problem was in my head.  I'm sorry for
being hardheaded.