Tuesday, April 21, 2015

All about Timezones in Oracle APEX.

I found an easy way to use local timestamps that does not depend on the timezone of the local computer or device.It works based on the centralized database timezone and lets the user configuration set the timezone.  Some devices might have the incorrect timezone inside of them.

Also, if different timezones are being used,  here is how to use the Central System Timezone or the local timezone when recording dates.

Basically


First we define a function...


create or replace FUNCTION LOCAL_DATETIME 
   RETURN DATE
   IS
   BEGIN
    return (TO_DATE(TO_CHAR (LOCALTIMESTAMP, 'DD-MON-YYYY HH12:MI:SSAM'),'DD-MON-YYYY HH12:MI:SSAM'));  
   END;

Then we store the region program that is being run in some sort of lookup table.
We can use this region information in the following command that only needs to be run once when the APEX app initializes...


ALTER SESSION SET TIME_ZONE = 'America/Chicago';
(America/Chicago is stored in the local timezone table)
Or in APEX
BEGIN
  APEX_UTIL.SET_SESSION_TIME_ZONE( P_TIME_ZONE =>  'America/Chicago');
END;


Whenever we want to record a timestamp, instead of using systdate, we use LOCAL_DATETIME 

Example :


BEGIN
  APEX_UTIL.SET_SESSION_TIME_ZONE( P_TIME_ZONE =>  'America/Chicago');  -- done once on apex app startup
END;
select LOCAL_DATETIME from dual;
21-APR-2015 11:35:52 AM


BEGIN
  APEX_UTIL.SET_SESSION_TIME_ZONE( P_TIME_ZONE =>  'America/Denver');  -- done once on apex app startup
END;
select LOCAL_DATETIME from dual;
21-APR-2015 12:36:52 AM

Here is a list of all the timezone strings.....
http://docs.oracle.com/cd/B13866_04/webconf.904/b10877/timezone.htm

Of Course if you use the Automatic timezone property in APEX, that sets the timezone for us.  

But if you select SYSDATE instead of LOCAL_DATETIME,  you will get the date/time of the database which is  

This part is true even if we use the automatic timezones inside of apex.....  Sysdate will always return the time of the app of the central database.
For me this is californa time. 
 
BEGIN
  APEX_UTIL.SET_SESSION_TIME_ZONE( P_TIME_ZONE =>  'America/Chicago');
END;

select to_char(LOCAL_DATETIME,'DD-MON-YYYY HH12:MI:SSAM') from dual;
21-APR-2015  12:35:52 AM

select to_char(SYSDATE,'DD-MON-YYYY HH12:MI:SSAM') from dual;
21-APR-2015 10:36:52 AM

Here is some more info......

http://nerd.net.au/oracle/29-apex-application-express/general-application/105-using-time-date-localisation-throughout-your-app

No comments:

Post a Comment