On 1/18/13, Steve Crawford <scrawford@pinpointresearch.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?
