Re: Understanding TIMESTAMP WITH TIME ZONE

I'd like to better understand TIMESTAMP WITH TIME ZONE.

My understanding is that, contrary to what the name sounds like, the
time zone is never stored. It simply stores a UTC timestamp,
identical to what TIMESTAMP WITHOUT TIME ZONE stores.

And then the only difference is that WITH TIME ZONE will allow you to
specify an offset in a literal value when INSERTing or UPDATEing ?
That sounds to me like a conversion or function - why is that a
different data type?

On 01/18/2013 09:31 AM, Robert James wrote:
> I'd like to better understand TIMESTAMP WITH TIME ZONE.
>
> My understanding is that, contrary to what the name sounds like, the
> time zone is never stored. It simply stores a UTC timestamp,
> identical to what TIMESTAMP WITHOUT TIME ZONE stores.
>
> And then the only difference is that WITH TIME ZONE will allow you to
> specify an offset in a literal value when INSERTing or UPDATEing ?
> That sounds to me like a conversion or function - why is that a
> different data type?

Probably for the same reason char and varchar are. They both just store
a string but in one the string is padded in the other it is not.
Basically WITH TIME ZONE tells Postgres that the field is time zone aware.

>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On 01/18/2013 09:31 AM, Robert James wrote:
> I'd like to better understand TIMESTAMP WITH TIME ZONE.
>
> My understanding is that, contrary to what the name sounds like, the
> time zone is never stored. It simply stores a UTC timestamp,
> identical to what TIMESTAMP WITHOUT TIME ZONE stores.
>
> And then the only difference is that WITH TIME ZONE will allow you to
> specify an offset in a literal value when INSERTing or UPDATEing ?
> That sounds to me like a conversion or function - why is that a
> different data type?
>
>
Though the type is called "timestamp with time zone" for historical
reasons, a better mental model is to think of that data type as a "point
in time." Think rocket launch, start of a conference-call, etc.

PostgreSQL happens to store the data internally as UTC but that is just
a reasonable and convenient way to store points in time and unimportant
from a user perspective. What is important is that the point in time can
be represented in whatever time zone is useful to the user. Furthermore,
PostgreSQL handles the daylight saving time (or European Summer Time or
...) rules applicable to the requested time zone.

select name, now() at time zone name from pg_timezone_names;

Note in the above that a "timestamptz at time zone somezone" returns a
value of type timestamp *without* time zone as you have provided both
the point-in time and the desired time zone.

In my work I find timestamp without time zone of little use but I can
see it being useful for events that are local-timezone-relative such as
"our stores are open from 9am to 5pm."

Date/time handling is tricky. It is worth spending some time reading and
re-reading the relevant sections of the manual - especially the warnings
- and playing with date/time manipulation till it "clicks."

Cheers,
Steve

On 1/18/13, Steve Crawford <scrawford(at)pinpointresearch(dot)com> wrote:
> On 01/18/2013 09:31 AM, Robert James wrote:
>> I'd like to better understand TIMESTAMP WITH TIME ZONE.
>>
>> My understanding is that, contrary to what the name sounds like, the
>> time zone is never stored. It simply stores a UTC timestamp,
>> identical to what TIMESTAMP WITHOUT TIME ZONE stores.
>>
>> And then the only difference is that WITH TIME ZONE will allow you to
>> specify an offset in a literal value when INSERTing or UPDATEing ?
>> That sounds to me like a conversion or function - why is that a
>> different data type?
>>
>>
> Though the type is called "timestamp with time zone" for historical
> reasons, a better mental model is to think of that data type as a "point
> in time." Think rocket launch, start of a conference-call, etc.
>
> PostgreSQL happens to store the data internally as UTC but that is just
> a reasonable and convenient way to store points in time and unimportant
> from a user perspective. What is important is that the point in time can
> be represented in whatever time zone is useful to the user. Furthermore,
> PostgreSQL handles the daylight saving time (or European Summer Time or
> ...) rules applicable to the requested time zone.
>
> In my work I find timestamp without time zone of little use but I can
> see it being useful for events that are local-timezone-relative such as
> "our stores are open from 9am to 5pm."

I'm confused. If I make sure to use UTC, isn't timestamp without time
zone identical, then? If not, what is the difference?

On 01/20/2013 04:28 PM, Robert James 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?

When you tag a date/time using WITH TIME ZONE you are telling Postgres
you care about time zones for that field:

http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html

"All timezone-aware dates and times are stored internally in UTC. They
are converted to local time in the zone specified by the TimeZone
configuration parameter before being displayed to the client"

So:

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 without time zone |

test=> insert into timestamp_test(id, ts_fld, ts_fld3) values(20,
now(), now());
INSERT 0 1

test=> SELECT * from timestamp_test ;
id | txt_fld | ts_fld | ts_fld2 |
ts_fld3
----+---------+-------------------------------+---------+----------------------------
20 | | 2013-01-20 16:43:02.060805-08 | | 2013-01-20
16:43:02.060805

Note how in the time zone aware field you get an offset.

>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On 01/20/2013 04:28 PM, Robert James wrote:
> On 1/18/13, Steve Crawford <scrawford(at)pinpointresearch(dot)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.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

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(at)pinpointresearch(dot)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(dot)klaver(at)gmail(dot)com

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(at)pinpointresearch(dot)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');

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) that the conversion is to UTC.
>
> Thanks!
> François Beausoleil
>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)gmail(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com