Almost all APEX applications I’ve been working with recently are used across multiple timezones, where many timezones uses DST (Daylight Saving Time) – that is basically almost all Europe and North America. The natural requirement is that users can see date+time information in their timezone time, reflecting if DST is active or not. Timezones and time conversions are always bit of mess and APEX is not supporting this completely out of box, but with small effort we can make our applications really global.

Prerequisites

Oracle 11 has extensive support for working with dates in multiple timezones, the key trick is to use data type TIMESTAMP WITH LOCAL TIMEZONE for all columns that contain date+time that needs to be globalized.  This data type  always storea timestamp with a timezone of the server (I strongly recommend to use GMT as server timezone), but when send to clients,  timestamps are always converted to a timezone of client db session. So all problem now reduces to provide correct timezone setting for users db session.

APEX leverages  this database functionality and you can set APEX session timezone (via APEX_UTILS.SET_SESSION_TIME_ZONE Procedure) that is then used for all db sessions within that APEX session.    Also for APEX application you can set “Automatic Time Zone” in the Application Properties / Globalization, however this is not working correctly for DST timezones (as of version 4.2)- see below.

Why APEX  “Automatic Time Zone” is not working properly

If “Automatic Time Zone” is set to yes, APEX tries to set APEX session timezone according to user browser setting.  When entering an application APEX automatically redirects to a special page, which has following Javascript:

function detectTimeZone(){
var lGmtHours = -(new Date()).getTimezoneOffset();
var lHours = parseInt(lGmtHours/60);
var lMinutes = lGmtHours%60;
window.location.href="f?p=301:1:0:&tz="+lHours+":"+(lMinutes<10?"0"+lMinutes:lMinutes);
}

As you can see this script  redirecta back to original page and it adds tz query parameter to the URL,   tz parameter is set to current hours offset  of local time from GMT and after redirection is used to set APEX session timezone to this offset.  And this is the source of problems for timezones with DST, because this offset is different in summer (when DST is on) and in winter.   So if you are using Automatic Time Zone enabled application in winter for instance,   all displayed timestamps from summer will be shifted by one hour from an expected time.

Proper DST handling

In order to handle DST timezones properly we have to set them in APEX session by their name (Europe/Berlin), which  assures that timestamp is converted with DST  in mind.   To help us detect user timezone name we can use jsTimezoneDetect library.

Here are steps how to assure proper automatic detection of user timezone name, that will work with DST zones (assuming that application is using login and is based on default 101 login page):

1. Disable Automatic Time Zone in the Application Properties / Globalization
2. Upload this JS script  https://bitbucket.org/pellepim/jstimezonedetect/downloads/jstz-1.0.4.min.js
   to Shared Components /Static files – file should not be attached to any application
3. Modify 101 Login page :
   1. Add  hidden page item – P101_TZ to Login region,   set Protect Value to No (important!)
   2. Add this code to Login Region Header

      <script type="text/javascript" src="#WORKSPACE_IMAGES#jstz-1.0.4.min.js"></script>
      <script type="text/javascript">
      $(function () {
      var tz = jstz.determine(); // Determines the time zone of the browser client
      $s("P101_TZ",  tz.name());
      });
      </script>

       

   3. Create page a Page Process just after Login process, named “Set TZ”, type PL/SQL anonymous block, with this code

      APEX_UTIL.SET_SESSION_TIME_ZONE(:P101_TZ);

       

The only small issue with this approach is that JS library chooses one  default name for each timezone, which may be different from ideal choice for user ( because there is no geo-location and one timezone can have many names according to major cities within that timezone). So for instance for Europe it always chooses Europe/Berlin for CET timezone, even if user might prefer Europe/Paris.