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:
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
- the database server and all systems using the database should be set to GMT.
- The timezone should be discarded from the timestamps selected from the database
- The OpenACS applications should allow flexibility in entering, displaying and updating dates in the user's prefered time zone.
- 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?