Skip site navigation (1) Skip section navigation (2)

Re: Unwanted time zone conversion

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Rob Richardson <RDRichardson(at)rad-con(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Unwanted time zone conversion
Date: 2012-05-14 15:46:27
Message-ID: 4FB128D3.9070905@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Francisco LeoveyDate: 2012-05-14 18:00:49
Subject: How to select by proximity
Previous:From: Rob RichardsonDate: 2012-05-14 14:30:27
Subject: Re: Unwanted time zone conversion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group