LinuxSA Mailing list archives

  From: Alan Kennington <akenning@dog.topology.org>
  To  : LinuxSA <linuxsa@linuxsa.org.au>
  Date: Fri, 17 Nov 2000 06:25:01 +1030

postgres pg_dump datetime timezone problem

Does anyone happen to know how to force
the Postgres utility pg_dump to dump dates
in GMT?

I'm having a problem with this.
I want to copy a database from one SuSE 6.2 system
to another.
But the format of the "datetime" data type seems to
always be in local time, not matter what I do with the TZ
env variable.

E.g. this is what I get on about version 6.5
INSERT INTO "food_hist" values ('Fri Nov 17 01:25:36 2000 CST',22,542);
INSERT INTO "food_hist" values ('Fri Nov 17 01:53:08 2000 CST',38,46);
INSERT INTO "food_hist" values ('Fri Nov 17 01:57:17 2000 CST',12,570);
INSERT INTO "food_hist" values ('Fri Nov 17 01:59:13 2000 CST',44,216);
INSERT INTO "food_hist" values ('Fri Nov 17 02:48:07 2000 CST',42,452);
INSERT INTO "food_hist" values ('Fri Nov 17 02:50:28 2000 CST',278,90);
INSERT INTO "food_hist" values ('Fri Nov 17 05:37:22 2000 CST',192,254);
INSERT INTO "food_hist" values ('Fri Nov 17 05:38:28 2000 CST',126,12); 

E.g. this is what I get on about version 6.4
68064   Fri Nov 17 01:25:36 2000 CST    22      542
68128   Fri Nov 17 01:53:08 2000 CST    38      46
68160   Fri Nov 17 01:57:17 2000 CST    12      570
68161   Fri Nov 17 01:59:13 2000 CST    44      216
68192   Fri Nov 17 02:48:07 2000 CST    42      452
68193   Fri Nov 17 02:50:28 2000 CST    278     90
68194   Fri Nov 17 05:37:22 2000 CST    192     254
68195   Fri Nov 17 05:38:28 2000 CST    126     12 

In both cases, it seems that the importing machine thinks
that CST means USA CST, and then converts this to GMT,
and then the Australian CST, resulting in a quite a
large error.

I would have updated to PostgreSQL version 7.0, except that
it came out a couple of days after I had updated 3 machines
to the latest version 6.x. 

Question:
Does version 7.0 have this date problem?
It's really a pg_dump problem, I think, not a general
Postgres problem.
If version 7.0 pg_dump has the ability to do UTC or whatever,
I'll update as soon as I get some of that mythical "spare time".

Cheers,
Alan Kennington.

PS. Please redirect any flames to Florida for counting.

-- 
LinuxSA WWW: http://www.linuxsa.org.au/  IRC: #linuxsa on irc.linux.org.au
To unsubscribe from the LinuxSA list:
  mail linuxsa-request@linuxsa.org.au with "unsubscribe" as the subject