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) On Thu, May 21, 2015 at 2:56 PM, Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> wrote: > On 05/21/2015 10:45 AM, Paul Jungwirth wrote: > > You really shouldn't use WITHOUT TIME ZONE. > > > I'd like to know more about this. Can you say why? > > > Start by reading about the date and time data types with special attention > to section 8.5.3: > www.postgresql.org/docs/current/static/datatype-datetime.html > > Now go back and read it again and experiment a while until it makes sense. > As Adrian Klaver so eloquently put it, "If I have learned anything about > dealing with dates and times, is that it is a set of exceptions bound > together by a few rules. Every time you think you have the little rascals > cornered, one gets away." This is also a very good reason to avoid > reinventing the wheel. > > When you need a break, watch this: > https://www.youtube.com/watch?v=-5wpm-gesOY > > His conclusion is a good one: be very happy that someone else has done the > dirty work for you. > > The Ruby article does make one good point which is that we are talking about > what they call an "instant" or what I like to refer to as a "point in time." > The "point in time" is actually a better way of thinking of "timestamp with > time zone" since the "timestamp with time zone" does not actually store any > timezone information - it stores a point in time that can be manipulated in > the time-zone of your choosing whereas timestamp without time zone is not a > point in time and must be combined with other information to do proper > manipulation. > > The article does also display a couple attitudes that I feel are especially > rampant in the web-development community. The first is that web developers > shouldn't become educated about the capabilities of a database but rather > use the database as a dumb data-store and redo everything themselves (often > this includes an utter failure to use the data-integrity capabilities of the > database). > > The second is the assumption that they are the only users of the database > and that nobody will ever access the data except through their > custom-written Ruby/PHP/Perl/Python code and that no other programming > language will ever be used. Woe be to the poor slob who has to deal with > ad-hoc queries, analytics platforms or reporting systems that weren't so > brilliantly reinvented or who wants to use range-types or other nice > PostgreSQL features. > > Internally PostgreSQL stores timestamp without time zone in UTC but that is > entirely irrelevant. What is relevant is that you can provide an > "instant"/"point in time" in whatever time-zone representation you want and > get it back the same way. Want to use a Unix epoch in your code. Go ahead: > extract(epoch from yourtstzcol) > abstime(yourepochint) > > Want to assume everything is UTC? No problem: > set timezone to 'UTC'; > > Then you can reinvent wheels to your heart's content without wrecking the > ability to easily use other tools. > > By the way, use full timezone names to avoid ambiguity. I don't know what > Ruby cooked up but PostgreSQL uses industry-standard names: > select * from pg_timezone_names; > > Your original question had to do with month/year. You will have to define > this for your use-case but beware that it won't necessarily get you away > from time-zone issues as the month ticks over on a zone-by-zone basis. > > Also note that time-intervals can be a source of interesting side-effects. > Operator precedence is important. For example, what is one month? 28-days? > 29? 30? 31? Every system must make a judgment call. Add a month to January > 31 and you will get February 28. But add/subtract a month from February 28 > and you get January 28/March 28. So you can create a query that takes a > date, adds a month and subtracts a month and results in a different date. > There is nothing to do here but to read the docs and try things. > > There are similar issues when crossing DST boundaries. If I want to push > something out a day in my time-zone on the day that DST changes I can do it > easily and understand that PostgreSQL will handle the extra/missing hour. Or > I can use an explicit increment of '24 hours' if that is what I want. No > extra steps of converting the timestamp without time zone to UTC, converting > that to the desired local zone, doing the calculations, converting back to > UTC and back to timezone without timestamp all the while potentially adding > an easy error such as doing things in the wrong order and checking for DST > changeover in the wrong time-zone. > > Cheers, > Steve > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
