Forum OpenACS Q&A: Re: timezone usage
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.
Can you be more specific as to where the data comes from? As I mention in https://openacs.org/forums/message-view?message_id=135904, 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.
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.