Hi Steve,
Thanks for such a thorough response! I agree that time is a lot trickier
and trappier than one might expect, so it's good to learn how others
grapple with it.
> Your original question had to do with month/year.
Just to clarify, that was Daniel's original question, but you're
replying to my follow-up question.
> 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).
That's not a debate I can hope to settle, but for what it's worth, I
mostly agree with you. That's why I've written these tools to let Rails
users leverage more of the capabilities inside Postgres, especially
integrity constraints:
https://github.com/pjungwir/db_leftovers
https://github.com/pjungwir/aggs_for_arrays/
also these efforts at education:
https://github.com/pjungwir/rails-and-sql-talk
http://illuminatedcomputing.com/posts/2015/02/postgres_lateral_join/
http://illuminatedcomputing.com/posts/2015/03/generate_series_for_time_series/
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.
Thanks again for your generosity!
Yours,
Paul
