pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMessed up time zones
17+ messages / 5 participants
[nested] [flat]
* Messed up time zones
@ 2012-08-03 08:31 Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 08:58 ` Re: Messed up time zones JC de Villa <jc.devilla@gmail.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
0 siblings, 2 replies; 17+ messages in thread
From: Laszlo Nagy @ 2012-08-03 08:31 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; +Cc: Daniel Fekete <dani@shopzeus.com>
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
@ 2012-08-03 08:58 ` JC de Villa <jc.devilla@gmail.com>
2012-08-03 09:18 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
1 sibling, 1 reply; 17+ messages in thread
From: JC de Villa @ 2012-08-03 08:58 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@shopzeus.com>; +Cc: pgsql-admin@postgresql.org
Re-sending this since I seem to have left out the list itself:
On Fri, Aug 3, 2012 at 4:31 PM, Laszlo Nagy <gandalf@shopzeus.com> 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<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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 08:58 ` Re: Messed up time zones JC de Villa <jc.devilla@gmail.com>
@ 2012-08-03 09:18 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 09:48 ` Re: Messed up time zones JC de Villa <jc.devilla@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Laszlo Nagy @ 2012-08-03 09:18 UTC (permalink / raw)
To: JC de Villa <jc.devilla@gmail.com>; +Cc: pgsql-admin@postgresql.org
>
> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 08:58 ` Re: Messed up time zones JC de Villa <jc.devilla@gmail.com>
2012-08-03 09:18 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
@ 2012-08-03 09:48 ` JC de Villa <jc.devilla@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: JC de Villa @ 2012-08-03 09:48 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@shopzeus.com>; +Cc: pgsql-admin@postgresql.org
On Fri, Aug 3, 2012 at 5:18 PM, Laszlo Nagy <gandalf@shopzeus.com> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
@ 2012-08-03 10:40 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
1 sibling, 1 reply; 17+ messages in thread
From: Laszlo Nagy @ 2012-08-03 10:40 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
@ 2012-08-03 10:55 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: Laszlo Nagy @ 2012-08-03 10:55 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
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)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
@ 2012-08-03 14:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:23 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2012-08-03 14:19 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@shopzeus.com>; +Cc: pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-08-03 15:23 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 15:34 ` Re: Messed up time zones Bill MacArthur <webmaster@dhs-club.com>
2012-08-03 16:20 ` Re: Messed up time zones Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 2 replies; 17+ messages in thread
From: Laszlo Nagy @ 2012-08-03 15:23 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:23 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
@ 2012-08-03 15:34 ` Bill MacArthur <webmaster@dhs-club.com>
2012-08-03 16:06 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
1 sibling, 1 reply; 17+ messages in thread
From: Bill MacArthur @ 2012-08-03 15:34 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:23 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 15:34 ` Re: Messed up time zones Bill MacArthur <webmaster@dhs-club.com>
@ 2012-08-03 16:06 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 16:38 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: Laszlo Nagy @ 2012-08-03 16:06 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:23 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 15:34 ` Re: Messed up time zones Bill MacArthur <webmaster@dhs-club.com>
2012-08-03 16:06 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
@ 2012-08-03 16:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 17:25 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 18:25 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
0 siblings, 2 replies; 17+ messages in thread
From: Tom Lane @ 2012-08-03 16:38 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@shopzeus.com>; +Cc: pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:23 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 15:34 ` Re: Messed up time zones Bill MacArthur <webmaster@dhs-club.com>
2012-08-03 16:06 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 16:38 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-08-03 17:25 ` Laszlo Nagy <gandalf@shopzeus.com>
1 sibling, 0 replies; 17+ messages in thread
From: Laszlo Nagy @ 2012-08-03 17:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@postgresql.org
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. :-)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:23 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 15:34 ` Re: Messed up time zones Bill MacArthur <webmaster@dhs-club.com>
2012-08-03 16:06 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 16:38 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-08-03 18:25 ` Laszlo Nagy <gandalf@shopzeus.com>
1 sibling, 0 replies; 17+ messages in thread
From: Laszlo Nagy @ 2012-08-03 18:25 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
> [ 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:23 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
@ 2012-08-03 16:20 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-08-03 17:21 ` Re: [ADMIN] Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
1 sibling, 1 reply; 17+ messages in thread
From: Steve Crawford @ 2012-08-03 16:20 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@shopzeus.com>; +Cc: pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [ADMIN] Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:23 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 16:20 ` Re: Messed up time zones Steve Crawford <scrawford@pinpointresearch.com>
@ 2012-08-03 17:21 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 18:25 ` Re: [ADMIN] Messed up time zones Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 17+ messages in thread
From: Laszlo Nagy @ 2012-08-03 17:21 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; pgsql-performance
> 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).
Yes, I know the zone. But I don't know the offset from UTC.
Example:
template1=> set timezone to 'UTC';
SET
template1=> select ('2011-10-30 01:00:00'::timestamptz) at time zone
'Europe/Budapest';
timezone
---------------------
2011-10-30 02:00:00 -- Is it winter or summer time?
(1 row)
template1=> select ('2011-10-30 00:00:00'::timestamptz) at time zone
'Europe/Budapest';
timezone
---------------------
2011-10-30 02:00:00 -- Is it winter or summer time? What is the
offset from UTC here? Can you tell me when it was in UTC?
(1 row)
template1=>
What is more:
template1=> select (('2011-10-30 00:00:00'::timestamptz) at time zone
'Europe/Budapest') is distinct from (('2011-10-30
01:00:00'::timestamptz) at time zone 'Europe/Budapest');
?column?
----------
f
(1 row)
template1=>
Yeah, we know what time zone it is in, but we don't know when it was,
thanks a lot. :-( It would be unambiguous to store the UTC offset along
with the value. But it is not how it was implemented.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [ADMIN] Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:23 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 16:20 ` Re: Messed up time zones Steve Crawford <scrawford@pinpointresearch.com>
2012-08-03 17:21 ` Re: [ADMIN] Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
@ 2012-08-03 18:25 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-08-03 18:37 ` Re: [ADMIN] Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
0 siblings, 1 reply; 17+ messages in thread
From: Steve Crawford @ 2012-08-03 18:25 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@shopzeus.com>; +Cc: pgsql-performance
On 08/03/2012 10:21 AM, Laszlo Nagy wrote:
>
>> 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).
> Yes, I know the zone. But I don't know the offset from UTC.
>
> Example:
>
> template1=> set timezone to 'UTC';
> SET
> template1=> select ('2011-10-30 01:00:00'::timestamptz) at time zone
> 'Europe/Budapest';
> timezone
> ---------------------
> 2011-10-30 02:00:00 -- Is it winter or summer time?
> (1 row)
>
> template1=> select ('2011-10-30 00:00:00'::timestamptz) at time zone
> 'Europe/Budapest';
> timezone
> ---------------------
> 2011-10-30 02:00:00 -- Is it winter or summer time? What is the
> offset from UTC here? Can you tell me when it was in UTC?
> (1 row)
>
> template1=>
>
I can not from the given information. Can you? The given information is
ambiguous as are all times during the hour of fall-back everywhere. That
leaves developers with a choice: choose an interpretation or throw an
error. PostgreSQL chooses to use an interpretation.
It would be nice if there were a specification as to how such ambiguous
data should be interpreted. Perhaps someone can point me to one and to
any relevant documentation detailing how PostgreSQL handles such data.
As it is, you need to be aware of how each part of your system deals
with such. For example (using my local time zone) using the date command
on Linux I see that
"date -d '2012-11-04 0130'"
returns
"Sun Nov 4 01:30:00 PDT 2012" (Still in Daylight Saving Time)
But given the same input, PostgreSQL interprets it as standard time
(offset -08):
select '2012-11-04 0130'::timestamptz;
timestamptz
------------------------
2012-11-04 01:30:00-08
> What is more:
>
> template1=> select (('2011-10-30 00:00:00'::timestamptz) at time zone
> 'Europe/Budapest') is distinct from (('2011-10-30
> 01:00:00'::timestamptz) at time zone 'Europe/Budapest');
> ?column?
> ----------
> f
> (1 row)
>
> template1=>
>
> Yeah, we know what time zone it is in, but we don't know when it was,
> thanks a lot. :-( It would be unambiguous to store the UTC offset
> along with the value. But it is not how it was implemented.
>
>
So you took two distinct points in time, threw away some critical
information, and are surprised why they are now equal? Then don't do
that. It's the equivalent of being surprised that www.microsoft.com is
the same as www.apple.com when comparing them on the short hostname
only. If you want to know if two points in time differ, just compare them.
Spending a couple hours reading
http://www.postgresql.org/docs/current/static/datatype-datetime.html
will be time well spent.
Cheers,
Steve
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [ADMIN] Messed up time zones
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:40 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Re: Messed up time zones Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:23 ` Re: Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 16:20 ` Re: Messed up time zones Steve Crawford <scrawford@pinpointresearch.com>
2012-08-03 17:21 ` Re: [ADMIN] Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 18:25 ` Re: [ADMIN] Messed up time zones Steve Crawford <scrawford@pinpointresearch.com>
@ 2012-08-03 18:37 ` Laszlo Nagy <gandalf@shopzeus.com>
0 siblings, 0 replies; 17+ messages in thread
From: Laszlo Nagy @ 2012-08-03 18:37 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-performance
> So you took two distinct points in time, threw away some critical
> information, and are surprised why they are now equal?
Well, I did not want to throw away any information. The actual
representation could be something like:
"2012-11-04 01:30:00-08 in Europe/Budapest, Winter time"
and
"2012-11-04 01:30:00-08 in Europe/Budapest, Summer time".
It would be unambiguous, everybody would know the time zone, the UTC
offset and the time value, and conversion back to UTC would be
unambiguous too.
I presumed that the representation is like that. But I was wrong. I have
checked other programming languages. As it turns out, nobody wants to
change the representation just because there can be an ambiguous hour in
every year. Now I think that most systems treat ambiguous time stamps as
if they were in standard time. And who am I to go against the main flow?
I'm sorry, I admit that the problem was in my head.
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2012-08-03 18:37 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-08-03 08:31 Messed up time zones Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 08:58 ` JC de Villa <jc.devilla@gmail.com>
2012-08-03 09:18 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 09:48 ` JC de Villa <jc.devilla@gmail.com>
2012-08-03 10:40 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 10:55 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 14:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 15:23 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 15:34 ` Bill MacArthur <webmaster@dhs-club.com>
2012-08-03 16:06 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 16:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-08-03 17:25 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 18:25 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 16:20 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-08-03 17:21 ` Laszlo Nagy <gandalf@shopzeus.com>
2012-08-03 18:25 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-08-03 18:37 ` Laszlo Nagy <gandalf@shopzeus.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox