Обсуждение: Unwanted time zone conversion
Greetings!
I am in the eastern US trying to track down events that occurred at a customer site in Vietnam. I decided it would be easier to look at their database on my machine instead of wrestling with a VNC connection half way around the world. So, I used PGAdmin to take a backup of their database, and then I restored it onto my computer. The table I’m interested in now has a column named event_date of type timestamp with time zone. On the customer’s computer, the time zone is +07. On my computer, the time zone is -04.
Is there a way to restore the database onto my computer, leaving the time zone in that column unchanged?
Thank you very much!
RobR
To answer my own question, at least partially: I added a new timestamp without time zone column to my copy of the database, and I copied the data from the event_date column into it using “at time zone ‘utc’ “. I have other times stored in the database in both local and UTC time, so the new column gives me direct correlation to times stored in UTC fields.
RobR
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Rob Richardson
Sent: Monday, May 14, 2012 9:57 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Unwanted time zone conversion
Greetings!
I am in the eastern US trying to track down events that occurred at a customer site in Vietnam. I decided it would be easier to look at their database on my machine instead of wrestling with a VNC connection half way around the world. So, I used PGAdmin to take a backup of their database, and then I restored it onto my computer. The table I’m interested in now has a column named event_date of type timestamp with time zone. On the customer’s computer, the time zone is +07. On my computer, the time zone is -04.
Is there a way to restore the database onto my computer, leaving the time zone in that column unchanged?
Thank you very much!
RobR
Dumping/restoring is unnecessary except for your convenience and has no effect on timestamp data.Greetings!
I am in the eastern US trying to track down events that occurred at a customer site in Vietnam. I decided it would be easier to look at their database on my machine instead of wrestling with a VNC connection half way around the world. So, I used PGAdmin to take a backup of their database, and then I restored it onto my computer. The table I’m interested in now has a column named event_date of type timestamp with time zone. On the customer’s computer, the time zone is +07. On my computer, the time zone is -04.
Is there a way to restore the database onto my computer, leaving the time zone in that column unchanged?
As to the (I believe historically badly named) "timestamp with time zone" data type, it is best to think of that data type as a "point in time". It does not actually hold any information about time zones. However when your client is set to a particular zone and you enter or extract data, the data will be automatically offset to a standard time zone (UTC in this case) so it can be easily converted to whatever time zone you require.
Your client is defaulting to displaying in your local time zone. The easiest thing for you to do is to set your client to the desired time zone, say:
set timezone to 'posix/Asia/Ho_Chi_Minh';
You can see the available names with:
select * from pg_timezone_names ;
Note that you can set specific offsets like:
set timezone to '-04';
But those are just hard offsets and will not account for daylight saving rules like true timezones will.
Cheers,
Steve