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'

'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?

Check out: http://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.

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.

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.

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

Can you be more specific as to where the data comes from?  As I mention in http://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.

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

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.

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