From: | Robert James <srobertjames(at)gmail(dot)com> |
---|---|
To: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Understanding TIMESTAMP WITH TIME ZONE |
Date: | 2013-01-21 00:28:24 |
Message-ID: | CAGYyBgizeb_v-eVUGWuJ80NbJq32S=7mfbAV36ZON0FMe-afWQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2013-01-21 00:47:37 | Re: Understanding TIMESTAMP WITH TIME ZONE |
Previous Message | Pavel Stehule | 2013-01-20 22:42:55 | Re: proposal: fix corner use case of variadic fuctions usage |