How do you show to people in different timezones the schedule in their own time?
That is the issue I had when building the wc2014challenge.com site.
So I started to just show the schedule in the "local time" of the stadium the match was in, so I didn't have to deal with the issue :)
But as you might think, people started to ask to see the schedule in their own time.
In previous years I solved the issue by adding a select list, so people could select the timezone they wanted to see the game in. Behind the scenes I reran the query and added the offset to the time - that worked just fine. Now the challenge this year is that the Brazilian timezone exist out of two timezones, so I couldn't really use the mechanism of before.
In the Oracle database, instead of a date column, you can use a timestamp with timezone column and can better calculate the difference. Another way is to use the "timestamp with local timezone", so you see the data in your timezone (after alter session set time_zone = your timezone).
Instead of doing the timezone conversion, I also thought of doing it on the client (browser), with momentjs for example.
They all have advantages and disadvantages... but at the end I decided to use the native APEX way.
Step 1: make sure your column is of type TIMESTAMP WITH LOCAL TIME ZONE:
Step 2: set Automatic Time Zone to Yes in "Edit Globalization Attributes" (Edit definition of your app).
And you are done!
Looking at the schedule it shows the times in my timezone, automatically. The nice thing is that this is cross application, so the calendar automatically shows the times in your timezone too. Very, very nice - no additional code.
So it's very easy to make your APEX application time zone aware... the only drawback I find is that this solution requires a redirect the first time you hit the site. That is not really good for Google rankings, but the advantages weight way more than that for now.
You can also read Joel's blog about automatic time zone in APEX 4.0, he build another example which you can follow.