Le 2013-01-20 à 20:04, Adrian Klaver a écrit :

> On 01/20/2013 04:28 PM, Robert James wrote:
>> On 1/18/13, Steve Crawford <scrawf...@pinpointresearch.com> wrote:
>> 
>> I'm confused.  If I make sure to use UTC, isn't timestamp without time
>> zone identical, then? If not, what is the difference?
>> 
> 
> Realized my previous explanation could be better. The primary difference is 
> that when you use WITH TIME ZONE Postgres stores the date/time as UTC and 
> knows it has done so. If you use WITHOUT TIME ZONE it does not. For purposes 
> of comparison it then makes the assumption the WITHOUT date/time data is 
> whatever is set for local time. In the situation you describe above you would 
> need to either set local time at UTC or use AT TIME ZONE to make the 
> correction.

I was curious as well, and I thank you for the clarification.

On my servers, TZ is set to Etc/UTC. I use exclusively use WITHOUT TIME ZONE, 
and in my queries, I use AT TIME ZONE to translate first to UTC, then to the 
needed time zone (America/Montreal for instance). That means I could save a 
call per row, and have queries run a bit faster. I'm talking about 1M rows or 
more per day.

Since TZ is set to Etc/UTC, a simple ALTER TABLE should translate everything in 
a single run?

ALTER TABLE x
ALTER COLUMN created_at
SET TYPE TIMESTAMP WITH TIME ZONE WITH (created_at AT TIME ZONE 'Etc/UTC');

Is that expected to be a long operation? Do I even need the WITH clause? PG 
should assume (correctly in this case) that the conversion is to UTC.

Thanks!
François Beausoleil

> -- 
> Adrian Klaver
> adrian.kla...@gmail.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to