Thursday, September 02, 2010

Automatic Time Zone support in Application Express 4.0

A feature of Application Express 4.0 which hasn't received a lot of press but is useful for those building applications that span time zones is the Automatic Time Zone application attribute.

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:

  1. How do we elegantly derive this for each end user of our application?
  2. 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:

execute immediate 'alter session set time_zone =''' || :MY_USER_TIMEZONE || '''';

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:

  1. 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.
  2. This time zone offset information will be sent to Application Express and recorded in the APEX session information for that user.
  3. 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 table tz_log(
id number primary key,
username varchar2(255) not null,
tz varchar2(512) not null,
created_ts timestamp not null );

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

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

insert into tz_log (username, tz) values(:APP_USER, apex_util.get_session_time_zone );

You can run this sample application here. Just keep in mind - it will require you to authenticate with your 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:

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.

You can also programmatically set and get the APEX session time zone setting using two new APIs in Application Express 4.0, namely APEX_UTIL.SET_SESSION_TIME_ZONE and APEX_UTIL.GET_SESSION_TIME_ZONE.


Peter Raganitsch said...

This really is a nice feature, i just needed something like that for an application running APEX 3.2 .

Another good reason to upgrade soon!

Stew Ashton said...

Just remember that once you have such a column with data in it, you can never again change the database time zone.

Byte64 said...

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.

Awaiting your insightful explanation ;-)

Sohil Bhavsar said...

Nice Post.

Thanks Joel for updating.

Sohil Bhavsar

Chris said...

Hi Joel!

It's great that APEX provides this feature declaratively now.

By the way, there is a possible vulnerability in the "execute immediate" line. An attacker could change other session parameters, too. Consider for example a value of

GMT' nls_date_format='"<script>alert(''XSS attack'');</script>"

for the :MY_USER_TIMEZONE item:

CN@cn> alter session set time_zone='GMT' nls_date_format='"<script>alert(''XSS attack'');</script>"';

CN@cn> select sysdate from dual;

<script>alert('XSS attack');</script>


Joel R. Kallman said...

@Stew - that is correct (about not changing the database time zone), but why would you ever want to?

@Flavio - Watch the Net traffic in Firebug when running an application with Automatic Time Zone enabled. You'll see a small JavaScript script which uses Date()).getTimezoneOffset().

@Chris - Good point. Another reason to use the declarative or programmatic methods to control your user's time zone.


Sohil Bhavsar said...

Hi Chris,

In which database version you had executed the code.

I had tried in Oracle 11g R2, using SQL Workshop in APEX 4.0

But couldn't get the output.

< script > alert('XSS attack'); < /script >


Sohil Bhavsar.

Chris said...

Hi Sohil,

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.


IvanZ said...

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?

Joel R. Kallman said...


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).


Joel R. Kallman said...


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.

I'll put up and example when 4.1.1 ships.

Thanks again for pointing out this deficiency in APEX.


IvanZ said...

Thnx 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 - 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.

Michael said...

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:

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?

Joel R. Kallman said...

Hi Michael,

Thanks for your comment and suggestion. We'll take a look at this library.


IvanZ said...

Automatic Time Zone is still not working correctly for DST time zones in Apex 4.2. However there is easy fix using library referred by Michael - some details how to do it are here