Automatic Time Zone support in Application Express 4.0

The Oracle database has this wonderfully rich data type called TIMESTAMP WITH LOCAL TIME ZONE. The elegance of this data type is that the value stored in this column will be displayed in the user's current database session time zone. Having written a PL/SQL package to do time zone conversion, it is a non-trival exercise to develop this type of functionality let alone maintain it. Wouldn't it be great if we could put this burden of maintaining constantly evolving time zone rules and daylight saving time dates on the database? Well, you get this for free with TIMESTAMP WITH LOCAL TIME ZONE.

So if all we need to do is set the database session time zone, then:

How do we elegantly derive this for each end user of our application?

How do we ensure that every page view and page submission in Application Express has its database session time zone set correctly for a particular user?

There were numerous suggestions in the past, of storing a user's preferred time zone as a preference and then authoring a PL/SQL block in the VPD attribute of an application like:

Not exactly obvious. And this still doesn't answer question #1 of how do we elegantly derive this. This is where the new Automatic Time Zone attribute is useful.

In the Application Builder, if you edit the Application Properties and navigate to the Globalization subtab, you should see something like:

By default, Automatic Time Zone will be set to 'No'. When set to 'Yes', this will now change the behavior of your application:

At the beginning of an Application Express session (which happens at the beginning each time a user runs your application ), the time zone offset will be calculated from their Web browser client.

This time zone offset information will be sent to Application Express and recorded in the APEX session information for that user.

Then, each and every page view for the duration of their APEX session, the Application Express engine will read this value and set the database session time zone to this value.

All you have to do is employ data types which are time zone aware (like TIMESTAMP WITH LOCAL TIME ZONE; DATE is not time zone aware) and check a box in your application definition. It couldn't be simpler!

To demonstrate this, I created a simple application using the following DDL:

create or replace trigger tz_log_trg1 before insert on tz_log for each rowbegin if :new.id is null then :new.id := to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'); end if; -- :new.created_ts := localtimestamp;end;

Then, I just built an application with a SQL report on this table and added an on New Instance PL/SQL Process of:

You can run this sample application here. Just keep in mind - it will require you to authenticate with your oracle.com credentials (the same credentials you use if you login to the OTN discussion forum) and it will record your visit in a log table, which others can view. Here's what it looks like - nothing fancy:

If you pay close attention, immediately after authentication, you'll see a URL like:

https://apex.oracle.com/pls/otn/f?p=27207:1:127976719236631&tz=-4:00

Obviously, your APEX session identifier and time zone value will be different than what I show above. But you'll see that there is a new parameter 'tz' to the 'f' procedure. And it is through this interface that you can create a URL to an APEX application and explicitly set the APEX session time zone to a different value. After you login, change the time zone value in the URL to something else (e.g., tz=0:00) and watch the values in the "Inserted into the Log Table (in your local time zone)" report column automatically adjust to that time zone. The underlying report definition didn't change - we're still simply selecting the TIMESTAMP WITH LOCAL TIME ZONE column out of the database, just now the database is automatically converting that value to display in the current session time zone.

Just remember that once you have such a column with data in it, you can never again change the database time zone.http://download.oracle.com/docs/cd/E11882_01/server.112/e10729/ch4datetime.htm#i1006705

Joel,it looks great but I fear I didn't fully understand how it works.What do you mean exactly by "the time zone offset will be calculated from their Web browser client"?

I implemented my own timezone handling in my latest effort and i agree with you, it's a rather pesky job, especially when you need to reconcile timestamps coming in from different sources like RSS feeds and then present the user with his own timezone offset. It's sounds trivial, but it isn't.

SQL Workshop is not the ideal environment to test that effect, better try sqlplus or create a small APEX sample application. You have to make sure that the select gets executed in the same session that was modified by the alter statement and that the default date to character conversion applies.

I used this feature, but I hit problem after change from DST to normal time.DB timezone is UTC (0:00) as recommended by Oracle. In summer Auto TZ set tz param in URL as tz=2:00 and times were correct, now Auto TZ set tz=1 and all historical timestamps are displayed incorrectly (one hour less).tz param can be only HH:MM format e.g. actual difference from UTC. So historical dates with DST offset, which is different are displayed incorrectly.Is there any way how to fix it? Am I missing something or is this feature really not aware of DST changes?

I actually think you're correct in that this feature does not sufficiently support Daylight Saving Time. To do this, we would need to be setting the session to a *region* and not necessarily an hour offset. And at this time, I'm not aware of a way to derive this region information automatically from JavaScript (like we can determine the timezone offset from JavaScript).

In the Application Express 4.1.1 patch set, the "session time zone" feature of Application Express will support the time zone region specification and not just UTC offsets. For example, in 4.1 and earlier, you can only specify values like 01:00, -05:00, etc. But in APEX 4.1.1 and later, you can specify time zone names like US/Eastern, America/New_York, Europe/Moscow.

Granted, the "Automatic Time Zone" feature of APEX will still only specify UTF offsets - we cannot derive the actual region via JavaScript. But if you can map your user's time zone offset to an actual time zone region, then you can call APEX_UTIL.SET_SESSION_TIME_ZONE in your application - and this can now be DST aware.

@JoelThnx for explanation. Actually I'm now on 4.0 - however your trick mentioned in the article worked for me - using 'alter session set timezone' in the VSP section of the application - thus I was able to set DST aware TZ name like US/Pacific.

For TZ name detection - I guess some JS library may do the trick. I've seen code like this https://github.com/dsimard/jskata/blob/master/src/jskata.timezone.js - which can detect if client TZ have DST and what is offset for standard time and DST. Based on this TZ name could be chosen from some predefined list. I guess this should work for most regular TZs.

Hi Joel,i think it would be very good if APEX Automatic Time Zone support could handle DST coreectly. I use the following JS to get a region name: http://www.pageloom.com/automatic-timezone-detection-with-javascript

Works great. Why not use this (or something similiar) in APEX?Perhaps also an additional checkbox to determine, if the developer wants this automatic conversion from offset to region?