Search Postgresql Archives

Re: Understanding TIMESTAMP WITH TIME ZONE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux