On Thu, May 21, 2015 at 2:10 PM, Paul Jungwirth <p...@illuminatedcomputing.com > wrote:
> Anyway, I agree that you have to store the time zone *somewhere*, and I > suppose that's the reason Joshua remarked that you really shouldn't use > WITHOUT TIME ZONE. And often a time has one perspective that is "canonical" > or "preferred", e.g. the time zone of the user who created the object. And > in that case WITH TIME ZONE gives you a convenient place to store that. I > think I still prefer a more "relativistic" approach where times have no > preferred perspective, and input strings are converted to a bare "instant" > as quickly as possible (using whatever time zone is appropriate). For > instance that avoids the failure scenario Brian described. I concede that > storing the time zone separately as a string makes it tricker for other > database clients, at least when the string is a name only meaningful to > Rails. In the future I'll keep an eye out for when WITH might be handy. And > maybe I'll do some research to see how well Rails would handle those > columns. > I'm not sure Brian is correct - but my head started to hurt when I attempted to reason it out - but what you've said above is incorrect. All "WITH TIME ZONE" does is tell PostgreSQL to apply timezone conversions during various operations. The stored data is represented as an epoch without any concept of the source data's timezone representation. i.e. if I store '2015-05-20T15:23:00-MST'::timestamptz into a table and later retrieve it I have no way to knowing that MST was part of the original specification. David J.
