Re: [ADMIN] Messed up time zones

[JC de Villa]

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

On Fri, Aug 3, 2012 at 4:31 PM, 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!
>
> 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
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin
>

Isn't:

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

supposed to be:

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

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


-- 
JC de Villa

Re: [ADMIN] Messed up time zones

[Laszlo Nagy]

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

Re: [ADMIN] Messed up time zones

[JC de Villa]

On Fri, Aug 3, 2012 at 5:18 PM, Laszlo Nagy  wrote:

>
>
>  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:

"Technically, PostgreSQL uses UT1because leap seconds are not handled."

Although there is a footnote on that page that states that:

"60 if leap seconds are implemented by the operating system".

-- 
JC de Villa

Re: [ADMIN] Messed up time zones

[Laszlo Nagy]

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', '-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', '-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



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Messed up time zones

[Laszlo Nagy]

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)



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Messed up time zones

[Tom Lane]

Laszlo Nagy  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

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Messed up time zones

[Laszlo Nagy]

On 2012-08-03 16:19, Tom Lane wrote:

Laszlo Nagy  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


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Messed up time zones

[Bill MacArthur]

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 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.


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.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Messed up time zones

[Laszlo Nagy]

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


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Messed up time zones

[Steve Crawford]

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


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Messed up time zones

[Tom Lane]

Laszlo Nagy  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

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Messed up time zones

[Laszlo Nagy]

2012.08.03. 18:38 keltezéssel, Tom Lane írta:

Laszlo Nagy  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. :-)



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Messed up time zones

[Laszlo Nagy]

> [ 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.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin