On Fri, Jul 8, 2011 at 11:13 AM, Stuart Bishop <stu...@stuartbishop.net> wrote: > On Mon, Jun 6, 2011 at 7:50 AM, Jim Nasby <j...@nasby.net> wrote: >> On Jun 4, 2011, at 3:56 AM, Greg Stark wrote: >>> On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby <j...@nasby.net> wrote: >>>> >>>> I'm torn between whether the type should store the original time or the >>>> original time converted to GMT. >>> >>> This is the wrong way to think about it. We *never* store time >>> "converted to GMT". When we want to represent a point in time we >>> represent it as seconds since the epoch. >> Right. Sorry, my bad. >> >>> The question here is how to represent more complex concepts than >>> simply points in time. I think the two concepts under discussion are >>> a) a composite type representing a point in time and a timezone it >>> should be interpreted in for operations and display and b) the >>> original input provided which is a text string with the constraint >>> that it's a valid input which can be interpreted as a point in time. >> >> My fear with A is that something could change that would make it impossible >> to actually get back to the time that was originally entered. For example, a >> new version of the timezone database could change something. Though, that >> problem also exists for timestamptz today, so presumably if it was much of >> an issue we'd have gotten complaints by now. > > The common problem is daylight savings time being declared or > cancelled. This happens numerous times throughout the year, often with > short notice. > > If you want to store '6pm July 3rd 2014 Pacific/Fiji', and want that > to keep meaning 6pm Fiji time no matter what decisions the Fijian > government makes over the next two years, you need to store the > wallclock (local) time and the timezone. The wallclock time remains > fixed, but the conversion to UTC may float. > > If you are storing an point in time that remains stable no matter > future political decisions, you store UTC time and an offset. The > conversion to wallclock time may float, and your 6pm Fiji time meeting > might change to 5pm or 7pm depending on the policical edicts.
This is a pretty good point. You would want the first of these (probably) for the time a meeting is scheduled to start, and the second for the time of a meteor shower (or some other natural event that doesn't care what the politicians decide). I feel like the second of these is pretty well handled by our existing timestamptz data type. Granted, you can't store the intended display time zone, but putting it in a separate column is not really a problem: at least, it has the right semantics. So maybe the first is the one we should be aiming at. If so, storing a counter and a time zone is the wrong approach: you need to record something like <year, month, day, hour, minute, second, tzname>. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
