4 Replies Latest reply on Jul 15, 2013 4:34 PM by Sergiusz Wolicki-Oracle

    XMLDB session timezone not the same as normal session timezone

    Andy Steel

      I'm using the XML DB features that provide access to PL/SQL Package procedures via a HTTP interface, listening on port 8080.
      Within the stored procedure, that's invoked from the HTTP request, I write to an audit table containing a TIMESTAMP field supplying the CURRENT_TIMESTAMP pseudocolumn as the value.

      LOGPRC VARCHAR2(128),
      LOGTXT VARCHAR2(4000),

      PROCEDURE WriteProcessLog(v_caller IN VARCHAR2, v_msg IN VARCHAR2)
      BEGIN -- Parent Transaction Suspended
      VALUES (current_timestamp, sys_context('userenv','sid'), SUBSTR(v_caller, 1, 128), gLogIndex, DBTIMEZONE || '/' || SESSIONTIMEZONE || ':' || SUBSTR(v_msg, 1, 4000));
      /* increment counter, wrap around at 64000 */
      gLogIndex := mod(gLogIndex + 1, 64000);
      /* commit autonomous transaction */
      END; -- Parent Transaction Resumes

      Since i'm in England and currently within Daylight Savings Time, the timestamp column is showing a value of -1 hour when I view the data.
      If I connect via SQLPlus and call the same stored procedure, then the value of the timestamp is correctly showing the BST time.

      This is a problem, because I will write to the file from normal PL/SQL session and then fire off a URL call (via another external system) back into port 8080 to conclude the transaction.
      When looking at the data in the table, the logging data pertaining to the port 8080 session is -1 hour as opposed to being the same timezone.

      I've logged out to the table data the DBTIMEZONE and SESSIONTIMEZONE pseudocolumns and they appear as follows:
      a) from the XML DB invocation: +00:00 and +00:00 (this is the wrong bit !!)
      b) from normal SQL Plus invocation: +00:00/+01:00

      Therefore my question is, how do I get the XML DB to use the correct timezone, taking into account daylight savings.
      Also how does SQL Plus know that i'm in daylight savings mode ?

      Here is my SQLPlus logon banner:
      SQL*Plus: Release Production on Wed Jun 5 17:00:26 2013

      Connected to:
      Oracle Database 11g Release - 64bit Production

      SQL> select dbtimezone, sessiontimezone from dual;

      +00:00 +01:00

      I've tried setting the environment variable ORA_STDZ as follows, in the script which starts oracle, but this has no effect.

      ORA_STDZ='Europe/London'; export ORA_STDZ

      I've also changed the column to LOGDTE TIMESTAMP WITH LOCAL TIME ZONE which solves the problem now, but when i'm looking at the data across GMT/BST boundaries it wouldn't look correct, since it would regress the times by -1 hours when I go back to GMT, so that's not acceptable.

      many thanks
        • 1. Re: XMLDB session timezone not the same as normal session timezone
          davidp 2
          I've responded in the SQL and PL/SQL forum {message:id=11056804}
          • 2. Re: XMLDB session timezone not the same as normal session timezone
            Sergiusz Wolicki-Oracle

            The behavior is expected. As the XMLDB session is created inside the kernel and there is really no database client involved (only HTML client, i.e. browser), there is nobody to set the session time zone. The session time zone remains set to its default value, which is '+00:00'. OCI and JDBC clients set the session time zone at login time based on the timezone of the client OS session. OCI also looks at the ORA_SDTZ environment setting.

            Note, OCI always sets the time zone to an offset time zone '+/-HH:MM'. Offset time zones are not DST-sensitive. That is, once you set the time zone to '+01:00', it will remain '+01:00' even if the database session exists across DST change moment. You can use ORA_SDTZ set to 'Europe/London', which is a region timezone, to make the offset automatically adjusted based on current day and month. JDBC will try to map OS regions to Oracle regions and fall back to offsets if mapping is impossible.

            For future dates, the choice between offset and region time zones affects the amount of work you need to do in case the time zone country, e.g. UK, decides to change the DST rules. You may need to adjust your stored dates in such case. Past dates are usually immune to DST rules changes. Note, TIMESTAMP WITH TIME ZONE will store the UTC time plus either time zone offset or time zone region, depending on which one was specified in the stored value.

            Regarding your application, if you use timestamps to coordinate events from various time zones, you should usually normalize the time you get from various clients to UTC/GMT and store such normalized value in a TIMESTAMP column or a TIMESTAMP WITH LOCAL TIME ZONE columns, if the DBTIMEZONE is '+00:00'. For example:
            VALUES (CURRENT_TIMESTAMP AT TIME ZONE '+00:00', sys_context('userenv','sid'), SUBSTR(v_caller, 1, 128), gLogIndex, SUBSTR(v_msg, 1, 4000));
            Only if the local time zone of the client is actually relevant, you should store it in a TIMESTAMP WITH TIME ZONE column.

            If you really need to determine the SESSIONTIMEZONE for a HTML client, you should include a mechanism for the user to specify the time zone of his preference and store it in user profile or a cookie. You can use the V$TIMEZONE_NAMES fixed view to find the time zone regions supported by Oracle Database. Then, execute ALTER SESSION SET TIME_ZONE at the beginning of each request to set the time zone specified by the user or use this time zone directly in datetime calculations.

            -- Sergiusz
            • 3. Re: XMLDB session timezone not the same as normal session timezone
              Andy Steel
              The first place where I could perform ALTER SESSION SET TIME_ZONE would be within the stored procedure, since there is no client from which to do it and it's directly called from the HTTP receiver.
              Can I perform ALTER SESSION SET TIME_ZONE within a stored procedure ? Maybe not exactly like this but dynamic SQL or some other means ?

              Otherwise, if I store all TIMESTAMPS as UTC/GMT then I would need the client to add +1hr (during Daylight savings) when viewing the records in the table. This would be done automatically presumably.

              I'll give that a try and see what happens when the clocks go back and forwards.

              Alternatively, if I can't get working, i'll store the date & time in DATE column and the milliseconds component in a numeric field - that's the original reason for using TIMESTAMP was to store milliseconds resolution.

              • 4. Re: XMLDB session timezone not the same as normal session timezone
                Sergiusz Wolicki-Oracle

                You can issue ALTER SESSION SET TIME_ZONE from a stored procedure using EXECUTE IMMEDIATE.


                I you want your stored time to be automatically adjusted to the session time zone, then you need to use TIMESTAMP WITH LOCAL TIME ZONE and make sure the database time zone is '+00:00' (i.e. UTC/GMT). Plane TIMESTAMP data type does not do any calculation by itself.  It is like a DATE data type except that fractional seconds are available.


                -- Sergiusz