Re: Understanding TIMESTAMP WITH TIME ZONE - Mailing list pgsql-general

On 01/20/2013 08:12 PM, François Beausoleil wrote:
>
> 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 <scrawford@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
Postgresstores the date/time as UTC and knows it has done so. If you use WITHOUT TIME ZONE it does not. For purposes of
comparisonit then makes the assumption the WITHOUT date/time data is whatever is set for local time. In the situation
youdescribe 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
totranslate first to UTC, then to the needed time zone (America/Montreal for instance). That means I could save a call
perrow, 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');

So you want to change your timestamp field to timestamp with time zone?
First run a test.
Since you are going from UTC to UTC you should not have to specify a
timezone.
FYI AT TIME ZONE has a gotcha if your local time and the AT TIME ZONE
are different:

I am in PST8PDT

test=> \d timestamp_test
            Table "public.timestamp_test"
  Column  |            Type             | Modifiers
---------+-----------------------------+-----------
  id      | integer                     | not null
  txt_fld | text                        |
  ts_fld  | timestamp with time zone    |
  ts_fld2 | timestamp(0) with time zone |
  ts_fld3 | timestamp with time zone    |


test=> SELECT now() AT TIME ZONE 'UTC';
           timezone
----------------------------
  2013-01-21 04:31:00.812022

Note no offset.

test=> INSERT INTO timestamp_test (id,ts_fld, ts_fld3) VALUES (1, now()
AT TIME ZONE 'UTC', now() AT TIME ZONE 'UTC');



test=> INSERT INTO timestamp_test (id,ts_fld, ts_fld3) VALUES (2, now()
AT TIME ZONE 'UTC', (now() AT TIME ZONE 'UTC') AT TIME ZONE 'UTC');

test=> SELECT * from timestamp_test ;
-[ RECORD 1 ]--------------------------
id      | 1
txt_fld |
ts_fld  | 2013-01-21 04:36:43.416164-08
ts_fld2 |
ts_fld3 | 2013-01-21 04:36:43.416164-08


The above has the timestamp  with the time string from UTC and the the
timezone as PST.

-[ RECORD 2 ]--------------------------
id      | 2
txt_fld |
ts_fld  | 2013-01-21 04:36:47.760131-08
ts_fld2 |
ts_fld3 | 2013-01-20 20:36:47.760131-08

The above is the second insert and it is actually what is desired.


>
> Is that expected to be a long operation? Do I even need the WITH clause? PG should assume (correctly in this case)
thatthe conversion is to UTC. 
>
> Thanks!
> François Beausoleil
>
>> --
>> Adrian Klaver
>> adrian.klaver@gmail.com
>


--
Adrian Klaver
adrian.klaver@gmail.com