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
