Thread: automatic time zone conversion
Hi, I'm trying to import some data into a table with a column defined as "timestamp not null". When I defined the table, postgres seemed to automatically convert the column to "timestamp with time zone not null", and I can't figure out how to get rid of the time zone information. I guess that's question one. Question two is about how to import data so that time zones are correct. I'm importing a bunch of dates that occurred in Sydney, Australia. The dates are spread throughout the year, so some will be in standard time and some in daylight time. What's the proper way to let postgres take care of this detail, figuring out which ones are standard time and which are daylight? If I don't do this, it seems that all my summer dates will be 1 hour off, or I'll have to manually figure out which dates are in which section of the year. Thanks. -Ken p.s. - apologies if this is already answered somewhere. I tried to search the archive, but got this error: "could not connect to server: Connection refused Is the server running on host db.postgresql.org and accepting TCP/IP connections on port 5433?"
On Wed, Jun 12, 2002 at 04:41:53PM +1000, Ken Williams wrote:
> Hi,
>
> I'm trying to import some data into a table with a column
> defined as "timestamp not null". When I defined the table,
> postgres seemed to automatically convert the column to
> "timestamp with time zone not null", and I can't figure out how
> to get rid of the time zone information.
I guess you could alter the schema so the type was "timestamp without time
zone" (IIRC).
> Question two is about how to import data so that time zones are
> correct. I'm importing a bunch of dates that occurred in
> Sydney, Australia. The dates are spread throughout the year, so
> some will be in standard time and some in daylight time. What's
> the proper way to let postgres take care of this detail,
> figuring out which ones are standard time and which are
> daylight? If I don't do this, it seems that all my summer dates
> will be 1 hour off, or I'll have to manually figure out which
> dates are in which section of the year.
Hmm, postgresql knows about daylight savings if your c library knows about
it. I'm not exactly sure how it works but you should investigate the PGTZ
environment variables. This is what happens on my 7.2.1 system:
select '02/06/2002 12:00:00 AEST'::timestamp;
timestamptz
------------------------
2002-06-02 12:00:00+10
(1 row)
select '02/03/2002 12:00:00 AEST'::timestamp;
timestamptz
------------------------
2002-03-02 13:00:00+11
(1 row)
which seems wrong to me...
HTH,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
On Wednesday, June 12, 2002, at 04:58 PM, Martijn van Oosterhout wrote:
> On Wed, Jun 12, 2002 at 04:41:53PM +1000, Ken Williams wrote:
>> Hi,
>>
>> I'm trying to import some data into a table with a column
>> defined as "timestamp not null". When I defined the table,
>> postgres seemed to automatically convert the column to
>> "timestamp with time zone not null", and I can't figure out how
>> to get rid of the time zone information.
>
> I guess you could alter the schema so the type was "timestamp
> without time
> zone" (IIRC).
Yeah, but watch this:
==============================================================
announce=# create table test (x timestamp without time zone);
CREATE
announce=# \d test
Table "test"
Attribute | Type | Modifier
-----------+--------------------------+----------
x | timestamp with time zone |
==============================================================
I don't seem to be able to get rid of the time zone any of the
ways I've tried.
-Ken
On Wed, Jun 12, 2002 at 05:12:56PM +1000, Ken Williams wrote:
> Yeah, but watch this:
>
> ==============================================================
> announce=# create table test (x timestamp without time zone);
> CREATE
> announce=# \d test
> Table "test"
> Attribute | Type | Modifier
> -----------+--------------------------+----------
> x | timestamp with time zone |
>
> ==============================================================
Ouch! What version is that? Here I get:
===========================================================
kleptog=# create table test2 (x timestamp without time zone);
CREATE
kleptog=# \d test2
Table "test2"
Column | Type | Modifiers
--------+-----------------------------+-----------
x | timestamp without time zone |
kleptog=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
===========================================================
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
Some Unix setting influencing these results ? Or some PostgreSQL- configuration setting that's disabled/enabled ? In postgresql.conf I could only see something like australian_timezones (commented out). No clue what that could possibly do. > -----Original Message----- > From: Martijn van Oosterhout [SMTP:kleptog@svana.org] > Sent: woensdag 12 juni 2002 10:23 > To: Ken Williams > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] automatic time zone conversion > > On Wed, Jun 12, 2002 at 05:12:56PM +1000, Ken Williams wrote: > > Yeah, but watch this: > > > > ============================================================== > > announce=# create table test (x timestamp without time zone); > > CREATE > > announce=# \d test > > Table "test" > > Attribute | Type | Modifier > > -----------+--------------------------+---------- > > x | timestamp with time zone | > > > > ============================================================== > > Ouch! What version is that? Here I get: > > =========================================================== > kleptog=# create table test2 (x timestamp without time zone); > CREATE > kleptog=# \d test2 > Table "test2" > Column | Type | Modifiers > --------+-----------------------------+----------- > x | timestamp without time zone | > > kleptog=# select version(); > version > --------------------------------------------------------------- > PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 > (1 row) > > =========================================================== > > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > There are 10 kinds of people in the world, those that can do binary > > arithmetic and those that can't. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
Martijn van Oosterhout <kleptog@svana.org> writes: > On Wed, Jun 12, 2002 at 05:12:56PM +1000, Ken Williams wrote: >> Yeah, but watch this: >> >> announce=# create table test (x timestamp without time zone); >> CREATE >> announce=# \d test >> Table "test" >> Attribute | Type | Modifier >> -----------+--------------------------+---------- >> x | timestamp with time zone | > Ouch! What version is that? Indeed that looks pretty broken. I'm wondering about version skew between psql and the backend, also. As far as the original issue goes, I suspect that Ken really does want timestamp with time zone anyway, especially if he's looking for correct handling of summer versus standard time. If he doesn't want timezones shown during display, that can be handled by casting to timestamp without time zone during SELECT; or there's always to_char(). The issue of which timezones to use might perhaps be related to needing to set AUSTRALIAN_TIMEZONES to true --- see http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/runtime-config.html#RUNTIME-CONFIG-GENERAL regards, tom lane
Martijn van Oosterhout <kleptog@svana.org> writes:
> Hmm, postgresql knows about daylight savings if your c library knows about
> it. I'm not exactly sure how it works but you should investigate the PGTZ
> environment variables. This is what happens on my 7.2.1 system:
> select '02/06/2002 12:00:00 AEST'::timestamp;
> timestamptz
> ------------------------
> 2002-06-02 12:00:00+10
> (1 row)
> select '02/03/2002 12:00:00 AEST'::timestamp;
> timestamptz
> ------------------------
> 2002-03-02 13:00:00+11
> (1 row)
> which seems wrong to me...
Looks okay to me. Since you specified the zone in both cases, you got
the same time-of-day in GMT terms in both cases. The stored internal
form was 02:00 GMT on each date (assuming your machine thinks that AEST
is GMT+10, like mine does). That was then rotated to your own local
time zone (evidently +10/+11) for display purposes.
If you want automatic handling of summer times the correct approach is
to leave off the timezone spec on entry, whereupon PG will intuit the
correct GMT offset for your timezone rules (as set by the TimeZone
setting). For instance:
test72=# set timezone to EST5EDT;
SET VARIABLE
test72=# select '02/03/2002 12:00:00'::timestamp with time zone;
timestamptz
------------------------
2002-02-03 12:00:00-05
(1 row)
test72=# select '06/03/2002 12:00:00'::timestamp with time zone;
timestamptz
------------------------
2002-06-03 12:00:00-04
(1 row)
regards, tom lane
...
> I don't seem to be able to get rid of the time zone any of the
> ways I've tried.
I'm pretty sure that you are running a pre-7.2 database. Upgrade to get
the proper "without time zone" behavior; before 7.2 there was no
"without time zone" type, and the parser folded everything into the one
type that was available.
But you really want time zones from what you have said. You just want
them to behave properly. So make sure that your system is running with
the correct time zone, and make sure that you have specified the GUC
option to use Australian time zones (read the docs to get my rant about
Australia having fully one quarter of the time zones in PostgreSQL ;)
- Thomas
On Wednesday, June 12, 2002, at 11:57 PM, Tom Lane wrote:
> If you want automatic handling of summer times the correct approach is
> to leave off the timezone spec on entry, whereupon PG will intuit the
> correct GMT offset for your timezone rules (as set by the TimeZone
> setting). For instance:
>
> test72=# set timezone to EST5EDT;
> SET VARIABLE
> test72=# select '02/03/2002 12:00:00'::timestamp with time zone;
> timestamptz
> ------------------------
> 2002-02-03 12:00:00-05
> (1 row)
>
> test72=# select '06/03/2002 12:00:00'::timestamp with time zone;
> timestamptz
> ------------------------
> 2002-06-03 12:00:00-04
> (1 row)
Aha - I think that's what I want. Thanks.
I'm getting incredibly confused about the zones, though,
probably because I'm in Australia. On my system, I see this:
[sa0110e0:~] kenw> env|grep -i z
TZ=EET-10EETDT,M10.5.0,M3.5.0
But what's EET? Postgres seems to define that as something in
Eastern Europe. Is there a conflict here? And where can I find
out what all the Mx.x.x stuff means? Is it system-dependent?
(This is AIX.)
Thanks.
-Ken
On Wednesday, June 12, 2002, at 11:57 PM, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: >> Hmm, postgresql knows about daylight savings if your c library >> knows about >> it. I'm not exactly sure how it works but you should >> investigate the PGTZ >> environment variables. This is what happens on my 7.2.1 system: > >> select '02/06/2002 12:00:00 AEST'::timestamp; >> timestamptz >> ------------------------ >> 2002-06-02 12:00:00+10 >> (1 row) > >> select '02/03/2002 12:00:00 AEST'::timestamp; >> timestamptz >> ------------------------ >> 2002-03-02 13:00:00+11 >> (1 row) > >> which seems wrong to me... > > Looks okay to me. Since you specified the zone in both cases, you got > the same time-of-day in GMT terms in both cases. The stored internal > form was 02:00 GMT on each date (assuming your machine thinks that AEST > is GMT+10, like mine does). That was then rotated to your own local > time zone (evidently +10/+11) for display purposes. What seems strange to me is that '02/03/2002 12:00:00 AEST' has any meaning at all. On March 2, Australia is observing DST, but AEST is Standard Time. Shouldn't there at least be a warning when trying to insert a date that doesn't exist? The results for '02/06/2002 12:00:00 AEST' look correct, because the time zone matches the reality. -Ken
Ken Williams <ken@mathforum.org> writes:
> What seems strange to me is that '02/03/2002 12:00:00 AEST' has
> any meaning at all. On March 2, Australia is observing DST, but
> AEST is Standard Time. Shouldn't there at least be a warning
> when trying to insert a date that doesn't exist?
I dunno. My experience is that people specify "0800 standard time"
when they mean standard time, regardless of daylight savings weirdness.
("0800 local time" would be the phrase for 8am summer-or-winter time.)
So the PG behavior does not seem out of line to me.
What I would like is for PG to accept "08:00 EST5EDT" as a way to
specify "8am USA east coast local time" regardless of what I have
TimeZone set to; and of course similarly for all other timezone specs
that are recognized by TimeZone. There does not seem to be any
reasonable way to accomplish that as long as we are stuck with the
C-library timezone API. But if we go over to maintaining our own
timezone library as pgsql-hackers have discussed recently, it'd be
doable. (I think ... Thomas, any thoughts?)
regards, tom lane
Ken Williams <ken@mathforum.org> writes:
> I'm getting incredibly confused about the zones, though,
> probably because I'm in Australia. On my system, I see this:
> [sa0110e0:~] kenw> env|grep -i z
> TZ=EET-10EETDT,M10.5.0,M3.5.0
> But what's EET? Postgres seems to define that as something in
> Eastern Europe. Is there a conflict here?
Looks like it. Perhaps EET needs to be another name that has
two different interpretations depending on AUSTRALIAN_TIMEZONES.
What fun...
regards, tom lane
On Wed, Jun 12, 2002 at 10:27:20PM -0400, Tom Lane wrote: > Ken Williams <ken@mathforum.org> writes: > > I'm getting incredibly confused about the zones, though, > > probably because I'm in Australia. On my system, I see this: > > > [sa0110e0:~] kenw> env|grep -i z > > TZ=EET-10EETDT,M10.5.0,M3.5.0 > > > But what's EET? Postgres seems to define that as something in > > Eastern Europe. Is there a conflict here? > > Looks like it. Perhaps EET needs to be another name that has > two different interpretations depending on AUSTRALIAN_TIMEZONES. > What fun... I would hve no problem with stripping out the whole australian timezone stuff, if only the following would work: # date --date='6 april 2002 12:00 AEST' date: invalid date april 2002 12:00 AEST' # date --date='6 april 2002 12:00 ACST' date: invalid date april 2002 12:00 ACST' # date --date='6 april 2002 12:00 ADST' date: invalid date april 2002 12:00 ADST' # date --date='6 april 2002 12:00 EST' Sun Apr 7 03:00:00 EST 2002 What timezone should we be using? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
...
> What seems strange to me is that '02/03/2002 12:00:00 AEST' has
> any meaning at all. On March 2, Australia is observing DST, but
> AEST is Standard Time. Shouldn't there at least be a warning
> when trying to insert a date that doesn't exist?
The date exists. The time zone *convention* exists. And you asked the
system to use those two. A warning at that point would just be pedantic
imho (and difficult and expensive to generate).
Other parts of this thread have given you some ideas on how to handle
these things consistantly, which seems to be a good start. You should
look at your docs to see how to compile 7.1.x with Australian time zone
conventions (I *think* that this was run-time parameterized for 7.2,
though it might have happened earlier). The docs have some information
on this (look in the User's Guide and in the appendix on "Date/Time
Support" for more details).
One of your other messages indicated that your TZ environment variable
is set as follows:
TZ=EET-10EETDT,M10.5.0,M3.5.0
which would indicate that you are specifying *all* of the time zone
rules through that environment variable. Are the rules you are using not
available in any of your system-supplied time zone databases? Of the
huge number of time zones we already support for Australia, this one has
never been mentioned before but could be supported if necessary. It
would be the fifth instance of a naming conflict between Australian
conventions and other areas of the world.
Hope things are working better for you now...
- Thomas