5 Replies Latest reply: Sep 29, 2010 4:43 PM by flavioc RSS

    Timezone and "Local"

    IckyIckyChiMoon
      In sqlplus when I run these to statements:

      alter session set time_zone = local ;
      select sessiontimezone from dual ;

      I get this:
      sql>  alter session set time_zone = local ;
      Session altered.
      
      sql>  select  sessiontimezone  from dual ;
      SESSIONTIMEZONE
      ---------------------------------------------------------------------------
      -06:00
      When I run the 2 statements from SQL Developer (Same PC), I get: "America/Denver" instead of the "-6:00".

      Is there a setting somewhere that I'm missing?

      Thanks.
        • 1. Re: Timezone and "Local"
          flavioc
          Hi,
          sql developer timezone TZ format is defined in the preferences/database/nls settings.
          If the mask ends with TZR, it means it will display the time zone region rather than a timezone offset.

          Probably sqlplus settings, either as environment variable or in the registry (in Windows) are defined differently.

          I can't check myself right now, but this should explain the behavior.

          Flavio

          ----
          http://oraclequirks.blogspot.com
          • 2. Re: Timezone and "Local"
            IckyIckyChiMoon
            I fiddled with the settings but it doesn't seem to do anything. To clarify my ?, I ran this in both SQL Developer and SQL*Plus:
            alter session set time_zone = local ;  
              select  sessiontimezone  
                from  dual ;
            alter session set time_zone = '-06:00' ;
              select  sessiontimezone  
                from  dual ;
            alter session set time_zone = 'US/Pacific'  ;
              select  sessiontimezone  
                from  dual ;
            alter session set time_zone = '04:00' ;
              select  sessiontimezone  
                from  dual ;
            In SD (ran as a script) I got these values:

            America/Denver
            -06:00
            US/Pacific
            +04:00

            In SQL*Plus I got these values:

            -06:00
            -06:00
            US/Pacific
            +04:00

            I didn't change any settings between statements. Notice that the only one that displays differently is the one that was set with "Local".

            I'm still puzzled.

            Edited by: SQL-Something on Sep 24, 2010 2:04 PM
            • 3. Re: Timezone and "Local"
              flavioc
              Here is the rather obscure definition taken from the SQL reference, ALTER SESSION chapter:

              +"Specify LOCAL to set the default local time zone offset of the current SQL session to the original default local time zone offset that was established when the current SQL session was started."+

              I must admit that at this hour of the night (for me) it's becoming increasingly difficult to decipher certain explanations.

              Flavio

              ----
              http://oraclequirks.blogspot.com
              • 4. Re: Timezone and "Local"
                IckyIckyChiMoon
                I think I've figured part of this out...

                There's a client side environment variable ORA_SDTZ. By default it wasn't even there, but setting it causes SQLplus to use it as the default TZ (offset or named TZ). If it's not there, I SUSPECT that it's pulling the TZ from the DB-server -- but I'd like to know for sure.

                Now for SQL Developer... the environment variable seems to have no effect on it. Again I'm SUSPECTING that it is because SD is using a JDBC thin connection and it bypasses the ora-client on my machine as well as the env-vars. So the question remains, where does SD pick up the default / "Local" TZ setting? It doesn't seem to be from my PC nor the DB server (of course it has to be in there somewhere -- it just doesn't match any of the settings that I'm seeing).

                I'm puzzled.
                • 5. Re: Timezone and "Local"
                  flavioc
                  did you read the following section of the [Database Globalization Guide|http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006728]?
                  SELECT sessiontimezone FROM DUAL;
                  what do you get from this query?

                  My current understanding is that it's a way of reverting the session time zone to the same value it had when the session commenced, that, in turn, is affected by the setting of ORA_SDTZ, if any.
                  If ORA_SDTZ is not specified, it will default to '+00:00' that, i guess, is like saying UTC time.

                  Flavio

                  ----
                  http://oraclequirks.blogspot.com