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
