This is going to be one of those posts where I talk about an issue I'm working with, but where I don't even pretend to have a definitive answer. So, what follows is what I've managed to figure out, but there may be a better way (in fact, I kind of hope there is).
My NaNoWriMo tracker has proven to be more popular than I originally expected–I now have over 50 registered users. And looking at my traffic sources, a problem occurred to me–I'd designed the application without considering the fact that it'd be used by people all over the world. And since almost all of its functionality is very time-focused, this was a problem.
The first thing I needed to fix was to change my view logic–there were several instances where I used sysdate (don't we all?), and those needed to be changed to current_date. sysdate gives the current database time, while current_date shifts it to the local time in the current session.
Of course, that requires that we actually be able to set the time zone for the current session. APEX gives us a handy procedure–apex_util.set_session_time_zone. This procedure does exactly what we want, though the timezone must be passed as an offset from UTC. More on that later.
Next, we need to provide the users with a list of timezones to chose from. I really struggled with this one; in the end, the best I could come up with was:
select tzname||' ('||tm||')' d, tzname r from (select distinct tn.tzname, substr(tz_offset(tn.tzname),1,6) tm from v$TIMEZONE_NAMES tn ) order by decode(substr(tm,1,1), '+', 24 + to_number(substr(tm,2,2)), 24 - to_number(substr(tm,2,2))) desc, to_number(substr(tm,5,2)) desc, tzname
This results in a very long list, but ensures that the users are able to find something that matches their location. Just storing the offsets would make for a smaller list, but then you have to worry about locations that don't observe Daylight Savings Time (or, conversely, those that do). tzname is varchar2(64), so I added that to my central users table; I could have added it to an application-specific config table, but someone's location isn't going to change based on which application they're using.
Finally, I created an application-level process to call apex_util.set_session_time_zone(tz_offset(…)) with the timezone stored in the user table. This ensures that the timezone is properly set, regardless of which page the user lands on.
The one feature I wasn't able to add that I wanted to was a field next to the timezone selector that would update when the user chose a new timezone and display the current time in that timezone. I'm not sure why this wasn't working; I tried several different dynamic actions, and found that on my test page, at least, it was using the original value of the select box instead of the selected value. I'm going to have to do some more testing to see if I can figure out what I was doing wrong there.