Search Postgresql Archives

Re: date with month and year

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

 



On Thu, May 21, 2015 at 2:10 PM, Paul Jungwirth <pj@xxxxxxxxxxxxxxxxxxxxxxxx> 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.


[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