Re: Unwanted time zone conversion

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