On 05/14/2012 06:57 AM, Rob Richardson wrote:
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?
Dumping/restoring is unnecessary except for your convenience and has no 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