> It's probably worth noting that both the Ruby 'best practice' AND > Postgres have a failure case when dealing with future dates precisely > because they are storing the data as UTC with a time zone. This is > one case where storing the data WITHOUT TIME ZONE would actually save > your bacon. > > From the postgres docs: "For times in the future, the assumption is > that the latest known rules for a given time zone will continue to be > observed indefinitely far into the future." > > Imagine scheduling a meeting for a certain time a few years from now. > This will be stored as UTC + time zone. A year later, that > government decides to change the time zone rules for their country. > Your operating system will get the new timezone data in an update (as > it should). However when the meeting comes around, you're going to be > early/late because the wall time that you get converting back from > UTC+time zone is no longer the time that you were supposed to have > been at the meeting. If you had stored that future date as a > timestamp WITHOUT time zone you would have still been on-time. > > This is only an issue for future dates, not past ones. > > -Brian Dunavant > (time is hard, so if I'm wrong anywhere here, someone please correct me) You are wrong (or me, but I'll try). > Imagine scheduling a meeting for a certain time a few years from now. > This will be stored as UTC + time zone. No it won't. It will store as UTC but will not store any timezone information (apart from the fact that it "knows" that what is stored on disk is converted to UTC from what the client sent in for storage). You are right in the following aspect: - client sends in "NOW at HERE" - server knows HERE = UTC+2 - hence NOW_UTC = NOW - 2 - server stores "NOW_UTC" - 2 years pass - government at HERE says that from today on "HERE = UTC + 4 - 2 years pass - client retrieves at HERE - server knows HERE = UTC + 4 - server also knows that HERE used to mean UTC + 2 - but server can not derive what HERE meant when NOW was stored ... - server converts stored NOW_UTC to HERE by doing NOW_UTC + 4 - client receives NOW_HERE but this became NOW - 2 + 4 IOW, the server would need to know what HERE meant when "now" was stored. This can only be solved by tracking insertion/update timestamps. Karsten -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
