5 Replies Latest reply on Mar 25, 2011 1:59 PM by 850384

    DB time zone

    user8792751
      Hi ,

      I have a question regarding the timezone
      1.     How does the oracle set the DBTIMEZONE?

      2.     Does the DBTIMEZONE changes accordingly whenever there is daylight savings time?

      3.     Does oracle 10g handles daylight savings time by itself? Or is any patch required for this to take effect?

      4.     Is this the same for oracle11g? We came to know oracle 11g takes care by itself and no patch is required since it is an built in feature.
        • 1. Re: DB time zone
          Helios-GunesEROL
          Hi;

          Please see below note and link
          http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/functions035.htm
          http://download.oracle.com/docs/cd/B28359_01/olap.111/b28126/dml_functions_1056.htm
          http://it.toolbox.com/blogs/database-solutions/were-entering-the-oracle-time-zone-7097

          Regard
          Helios
          • 2. Re: DB time zone
            12cdb
            Probably this can help:

            http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#i1009404
            http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006667
            • 3. Re: DB time zone
              Helios-GunesEROL
              Hi;

              Please also see:
              Timestamps & time zones - Frequently Asked Questions [ID 340512.1]
              http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm

              Regard
              Helios
              • 4. Re: DB time zone
                user8792751
                Hi,

                i have enabled daylight saving option in the windows date/time function.

                here the time zone is -06.00


                in the database , the dbtimezone is showing as -6
                SQL> select dbtimezone from dual;
                
                DBTIME
                ------
                -06:00
                But when i check the timestamp it is showing as -05

                what could be the reasone behind this?

                How to change the timestamp from -5 to -6
                SQL> select systimestamp from dual;
                
                SYSTIMESTAMP
                ---------------------------------------------------------------------------
                25-MAR-11 06.47.46.093000 AM -05:00
                
                SQL> select dbtimezone from dual;
                
                DBTIME
                ------
                -06:00
                
                
                
                
                SQL> select dbtimezone from dual;
                
                DBTIME
                ------
                -06:00
                • 5. Re: DB time zone
                  850384
                  The post above your question has the answer. It has to do with the environment that your client runs in.
                  Please see [http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006728]

                  When you start your client software to access a database, the local environment variables are used to set the timezone used for converting date and timestamps for your local display.

                  Hope that helps.