Forum OpenACS Q&A: Re: timezone usage
As I mentioned, I found it difficult to reason about time in different timezones in sql in pg. I found it useful to perform most queries in GMT, but to carry the user local date and time around for each object as separate fields in addition to the GMT timestamp.
Doing so made it simpler for me to make queries from the user point of view.
It also makes it much easier to debug and assure yourself that the timezone logic is working, and/or to fix your db and correct it when it is not.
Jerry, I was thinking the same thing about keeping the timezone separate. It is just stupid that this information is destroyed, considering how much information is contained in the timezone part.
However what is probably needed is something much simpler than the 11 megabytes of data in the ref-timezones data model. Users should be able to say this event takes place in this timezone at this local time. But if the timezone they are looking for doesn't exist, maybe they could add one? We obviously cannot rely on either unix, tcl or pg to handle this properly and in a user friendly way.
So I'll have to look at what would be needed to use the clock format command. I think it takes a seconds input, so these friendly timezones could be formatted as a number of seconds offset from gmt, I think. Also what would be needed to do this in the db? Sorry mumbling out loud.
Yes, I deleted all timezone information prior to 2002 on my system, AND I also deleted all timezone information after 2008! The timezone data went way up to 2038, I think, but the truth is that due to local political issues, volcanism, and global issues such as energy crises and asteroid strikes, locales do change their timezone information from year to year. It needs to be maintained, and similarly, anything to remove records from joins gots to be a good thing, right? But where did the data come from?
I also added a variety of "obvious" indices that aren't included in the tz data. Are they useful? Oh, uh, sure. (I don't know, but the plan looks good.)
I believe my experience with [clock scan ... ] wasn't promising. IIRC it understands timezones as gmt offsets ("-07", etc.), but not timezones as in "PST".
I am not sanguine about allowing users to create their own timezone data. Maybe, but not for critical functionality. (Spring Ahead, starve a fever, I can never quite remember.)
It would be interesting to get a few people working on such projects to discuss what a lowlevel APIs would be useful, and to discuss what could be added to various data models to make such stuff efficient.
And.... It seems to me that an almost mandatory adjunct of local time sensitive webfeatures is the creation of a local time sensitive OACS cron job or something similar....
"Run this task at 2:32 AM Tajikistan time....", "At 11PM local time, where are my kids?", or "It's 3:30AM GMT, run local time jobs...."
Jerry, when users input times, they need to know the timezone. If it is a personal calendar, then maybe a global config could 'assume until told otherwise' that the times are in the user's currently configured timezone. But as long as users are doing the input, they need to know the info, there is no way around that.
clock scan takes a subset of available timezones, I think, the pg data in ref-timezones seems even more limited. I know this because I picked timezone names off the internet and tried them with clock scan, and using them as input to pg.
Anyway, timezones only matter to the user, and only the inputing user knows if that data is correct, so having them create their own timezones, with offsets should be okay. All I'm talking about is a timezone name and offset, in seconds from GMT. Users could also have their own private timezone names. Instead of having to always remember PDT from PST, why not 'Pacific Winter', 'Pacific Summer', or 'My Company Home Office'? For online communities, it is important to consider that users might want to switch timezones on the displayed dates to make sure they know when events are happening in their local time, or East Coast time, or when it will be when they get to their destination in some other location.
Said another way: timezones provide context for an absolute time GMT. If users can switch the context at will, it is more likely they input data correctly, or update it later on, or just figure out what it means.