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 thenI 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 (AT) postgresql (DOT) org [mailtogsql-novice-owner (AT) postgresql (DOT) org] On Behalf Of Rob Richardson
Sent: Monday, May 14, 2012 9:57 AM
To: pgsql-novice (AT) postgresql (DOT) 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 thenI 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

On 05/14/2012 06:57 AM, Rob Richardson wrote:
effect on timestamp data.

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