Forum OpenACS Q&A: Re: timezone usage

12: Re: timezone usage (response to 1)
Posted by Don Baccus on
1.  The timezone data in ref-timezones comes from Unix/Linux, which is why it goes up until 2038, which is "Y2K" in Unix-speak.  It is as complete as that data is, and I assume that data's quite complete as it is used when you install RedHat, Mandrake etc ad nauseum, when it asks for your timezone.  Is there any particular timezone you feel is missing?  To make things even more problematic I do believe the timezone designations themselves are an ISO standard ... they therefore should be complete as of the date of adoption.  PG's internal dataset of timezones seems incomplete.  Regardless, ref-timezones gives us a way to deal with them that works with both PG and Oracle, since there's no common overlap within the two RDBMS's for us to lean on.  Unfortunately.  Though Oracle's moving in the direction of supporting SQL 92 timestamps, hmmm ...

2. I don't see any value in allowing folks to enter their own timezone stuff.

3. As far as recurring events goes, study the iCalendar standard for an industry point-of-view on how to handle them.  The short story is that you define an event and recurrance interval, then pass along all the relevant timezone information to the calendaring client, which can then do the transformations without pretending to know anything about timezone the originator lives in.

4. Getting this stuff right both in Oracle and PG is tricky.  Knowing what I know now, we would've used "timestamp without timezone" for internal storage.  But when we began porting, "timestamp" included timezone information and if PG supported "timestamp without timezone" in early days, it wasn't documented clearly (if at all, and to be honest I think this option was added fairly recently).  We're living with that decision, which unfortunately makes things more complex.  Perhaps in 5.1 we can move away from "timestamp with timezone" and provide upgrade scripts ... it would simplify life, no doubt.

5. In PG you have to understand that timestamps are always displayed with timezone information.  Use of the utilities in ref-timezones ought to make life easier but you need to remember to display with the timezone information chopped off or skewed manually to fool the server-local timestamp displaz into showing the user's local timezone.

13: Re: timezone usage (response to 12)
Posted by Jerry Asher on

Can you be more specific as to where the data comes from?  As I mention in, I believe the tables in the OACS have some bad records.  Also, it is important to get new tables every year as timezone rules do change from locale to locale.  I would like to support my clients with the newer data.

14: Re: timezone usage (response to 13)
Posted by Tom Jackson on

Jerry, I believe the data was somehow extracted from the unix timezone files/database. Unfortunately the tool that did the work doesn't seem to be included with the ref-timezone package. If we had the tool, then it might be possible to only populate the database with the required timezones.

After considering the need for repeated events and the use of intervals, timezone data is going to be required.

I'm wondering if in the future we could hack to_char to output a date in the timezone we want, assuming data is in GMT.