Forum OpenACS Q&A: timezone usage

Collapse
1: timezone usage
Posted by Tom Jackson on 10/19/03 01:21 AM

Is anyone running a time based application where users are in different time zones? I started working on a date/time entry filter and there are some interesting issues when getting data from and to the database.

One issue is that postgresql returns dates in the current server time zone. So if I enter a date as a string like 

'2003-10-17 00:00:00 GMT'
And if my system is setup to PDT, the data comes back from the database like this: 
'2003-10-16 17:00:00-7'

There don't seem to be any tcl or OpenACS facilities for working with the numberic time zone, and actually there are probably dozens of named time zones that would correspond to -7. Also users may want to view data relative to their home or current time zone, not PDT or GMT.

So I thought I'd look around for a list of time zones to at least see what is valid to go into something like 'clock scan', but these tcl commands just use the os libraries and I was unable to find a list of accepted time zones and the corresponding offset.

My impression is that

  1. the database server and all systems using the database should be set to GMT.
  2. The timezone should be discarded from the timestamps selected from the database
  3. The OpenACS applications should allow flexibility in entering, displaying and updating dates in the user's prefered time zone.
  4. timestamps should be converted back to GMT before going to the database. (this happens anyway, but if #3 converts the time zone the application needs to remember which time zone the data is being displayed in)

This is only necessary for time based applications where users can't be forced to use a single time zone. I haven't looked at Oracle yet. What are the issues there?

Collapse
2: Re: timezone usage (response to 1)
Posted by Tom Jackson on 10/19/03 07:29 AM

The reference package has data about timezones and rules to convert to UTC. The package also has functions in pl for using the rules. Is this used anywhere in OpenACS?

Collapse
3: Re: timezone usage (response to 1)
Posted by Jerry Asher on 10/20/03 10:40 AM
Tom,

Check out: https://openacs.org/forums/message-view?message_id=131332

I am doing this now for a site that will take reservations worldwide for resources based on the local time of the resource.

It sure ain't pretty.  I mainly punted on the PG 7.2.3 functions -- they do not see to be a complete timezone algebra in the mathematical sense.  (Or just not very clear.)

I end up doing a lot of string manipulation.

Worse is tonight's showstopper -- maybe I am too tired, but the data in timezone refs seems to be incorrect.  More on that in the next post.

Also, speak to Simon Carstensen, I think he is doing some work in that area.

Collapse
4: Re: timezone usage (response to 1)
Posted by Jerry Asher on 10/21/03 06:53 PM
Tom,

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.

Collapse
5: Re: timezone usage (response to 4)
Posted by Tom Jackson on 10/21/03 07:27 PM

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.

Collapse
6: Re: timezone usage (response to 5)
Posted by Jerry Asher on 10/22/03 01:47 AM
A couple of issues....

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...."

Collapse
7: Re: timezone usage (response to 6)
Posted by Tom Jackson on 10/22/03 02:40 AM

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.

Collapse
8: Re: timezone usage (response to 1)
Posted by Dirk Gomez on 10/22/03 08:27 AM
Peter has "timezone"-ized the calendar that is going to be shipped with
5.0. He hasn't documented it yet, so that may be a good opportunity for him to
shed some light on his implementation ;)
Collapse
9: Re: timezone usage (response to 1)
Posted by carl garland on 10/22/03 03:45 PM
See https://openacs.org/forums/message-view?message_id=20831
for some discussion on this and if anyone is interested
in chatting about this ping me in IRC OpenACS room.
Collapse
10: Re: timezone usage (response to 9)
Posted by Tom Jackson on 10/22/03 06:15 PM

So far we have been talking about one case: a static date. I assume that the Calendar package and events in general have to deal with recurring activities. This is a problem if an interval is used to calculate future occurrences, because eventually you will bump up against a timezone boundary. At this point intervals loose meaning unless you know the timezone where the event is occurring. I haven't looked at how the events module handles this, does anyone know?

I'm wondering if there is some better way to deal with recurring activities besides using intervals? Humans understand 'every Tuesday at 8 p.m.', but since intervals are fixed length, they have to be adjusted to cross timezones.

So it seems to work with recurring activities, you need both timezone tables, and a way to record the timezone of the activity.

Collapse
11: Re: timezone usage (response to 10)
Posted by Jerry Asher on 10/23/03 04:57 AM
I think this is what I was alluding to earlier.  Some events are dependent on the user's local time zone, whatever that might be at the moment, and some are dependent on a fixed geographical time zone.

On a side note, when is a day not even close to 86,400 seconds?

I found a bug in my app last night where it displayed Sunday twice next week.  Very odd.  Until I discovered that for tcl, next Sunday where DST comes into effect is a day with 90,000 seconds.  It was not suff for me to just add 86,400 to the last clock scanned val, I needed to add "1 day" to it.  Presumably there's a day in April with only 82,800 seconds....

Collapse
12: Re: timezone usage (response to 1)
Posted by Don Baccus on 10/23/03 10:22 AM
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.

Collapse
13: Re: timezone usage (response to 12)
Posted by Jerry Asher on 10/23/03 07:38 PM
Don,

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.

Collapse
14: Re: timezone usage (response to 13)
Posted by Tom Jackson on 10/23/03 07:58 PM

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.

Collapse
15: Re: timezone usage (response to 1)
Posted by Jon Griffin on 10/23/03 08:15 PM
There used to be a python script that extracted the data. It was possibly from Solaris, but if I remember it was from a RH box.

If anyone has a ACS 4 classic setup it should be there. Also, it might show up in CVS on OACS.

Collapse
16: Re: timezone usage (response to 15)
Posted by Jeff Davis on 10/23/03 09:33 PM
when we originally pulled this data out in london we did 
zdump -v `find /usr/share/zoneinfo/ -type f -print`
(well, more or less, it seems like there is also /usr/share/zoneinfo/right and /usr/share/zoneinfo/posix which duplicates a lot of the data). We then had a 3 or 4 line perl script to convert it to insert statements. With the new "copy" format I think it should be pretty easy to recreate the data.
Collapse
17: Re: timezone usage (response to 1)
Posted by Pedro Liska on 04/02/07 07:44 PM
I'm planning on writing a script that will load the timezone info from my Linux box into the timezone and timezone_data OpenACS tables.

I'll obtain the rows for the timezones table doing a:
find /usr/share/zoneinfo/ -type f -print | grep -v "/right/" | grep -v "/posix"

Notice this will ignore the /usr/share/zoneinfo/posix directory, the /usr/share/zoneinfo/posixrules file, and the /usr/share/zoneinfo/right directory. I'm not quite sure what they do.

I'll then populate the timezone_rules table with:
zdump -v `find /usr/share/zoneinfo/ -type f -print | grep -v "/right/" | grep -v "/posix"`

(Thanks Jeff Davis posting how to use the zdump command on this thread)

I'm just wondering if someone has already done this. Or if you think I'll run into some problems when doing this. I believe this will give me all the timezones and timezone rules Linux is aware of.

Collapse
18: Re: timezone usage (response to 1)
Posted by Pedro Liska on 04/13/07 11:31 PM
My script is ready! You may find it here http://pedroliska.com/files/load-timezones.tcl.txt

It uses the files in the /usr/share/zoneinfo directory and the zdump command to populate the timezones and timezone_rules OpenACS tables. I'm running Red Hat ES 3, and have not tested it in other Linux flavors.

I think it's a very useful utility and many users will want to use it. So hopefully someone will commit it to the OpenACS CVS tree.

Enjoy!

-- Pedro

Collapse
19: Re: timezone usage (response to 18)
Posted by Pedro Liska on 04/13/07 11:47 PM
I've only tested the script in Oracle =(. Since I'm using an Oracle sequence, I think it just won't run in PostgreSQL. Or will it?
Collapse
20: Re: timezone usage (response to 18)
Posted by Don Baccus on 04/19/07 11:41 AM
What would be really helpful would be to modify the script to generate the data in sql/common, which was originally derived from the linux files. In this way fresh installs get the new data, and we can easily write an upgrade script to empty the table and reload it on a version upgrade.
Collapse
21: Re: timezone usage (response to 20)
Posted by Pedro Liska on 04/28/07 01:08 AM
You're right Don. I updated my script to generate such files: timezones.dat and timezones-rules.dat.

My goal was to get the current *.ctl files in ref-timezones package to be able to load my .dat files but I ran into a problem with generating the timezones-rules.dat file. I was not able to get Tcl's clock procs to format the date to "Mon DD YYYY HH:MI:SS" format. So you have to modify it's .ctl file to load it. The date is in the following format: "Dy Mon DD HH24:MI:SS YYYY".

I'm assuming PostgreSQL can also load such format but I have not tested that.

I uploaded the script to the same location than last time: http://pedroliska.com/files/load-timezones.tcl.txt