9 Replies Latest reply: Jan 3, 2013 6:10 AM by Chanchal Wankhade RSS

    Reg : DB Timezone -

    ranit B
      Hi Experts,

      I'm stuck up in a situation where I need the TimeZone Offset name but I'm getting Offset number .
      SELECT SYSTIMESTAMP ts FROM DUAL;
      
      1/3/2013 4:18:45.855158 AM -06:00
      But, in place of -06:00 I need CST or IST ...

      I tried many TimeZone functions and also altering the +'NLS_TIMESTAMP_TZ_FORMAT'+ to +'DD-MON-RR HH.MI.SSXFF AM TZR'+
      Any pointers?

      My Database version :
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE     11.2.0.2.0     Production
      TNS for Solaris: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
      Ranit B.
        • 1. Re: Reg : DB Timezone -
          Niket Kumar
          SQL> SELECT SYSTIMESTAMP AT TIME ZONE 'CST' FROM dual;

          SYSTIMESTAMPATTIMEZONE'CST'
          ---------------------------------------------------------------------------
          03-JAN-13 04.25.55.622000 AM CST

          SQL>

          http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions190.htm
          • 2. Re: Reg : DB Timezone -
            Keith Jamieson
            I did a quick google and found this


            Get time zone abbreviations
            • 3. Re: Reg : DB Timezone -
              ranit B
              Thanks Keith, that was useful.

              But got a doubt regarding what Gasparatto explained.
              To have a valid timezone abbreviation you need to set a time_zone :
              
              SQL> alter session set time_zone = 'cet';
              
              Session altered.
              Actually what is this Time_Zone and altering that can it affect anywhere else in database ?
              I mean if the database is moved from Canada to UK, how will this parameter time_zone behave ?

              Currently, I'm getting this as UNK (i.e. Unknown)

              @Niket -
              That didn't help. I'm still getting -6:00
              • 4. Re: Reg : DB Timezone -
                Chanchal Wankhade
                Hi,

                I can see the desired result.
                SQL> select banner from v$version;
                
                BANNER
                ----------------------------------------------------------------
                Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
                PL/SQL Release 10.2.0.4.0 - Production
                CORE    10.2.0.4.0      Production
                TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
                NLSRTL Version 10.2.0.4.0 - Production
                
                SQL> select SYSTIMESTAMP from dual;
                
                SYSTIMESTAMP
                ---------------------------------------------------------------------------
                03-JAN-13 04.26.51.972000 PM +05:30
                
                SQL> SELECT SYSTIMESTAMP AT TIME ZONE 'CST' FROM dual;
                
                SYSTIMESTAMPATTIMEZONE'CST'
                ---------------------------------------------------------------------------
                03-JAN-13 04.56.54.237000 AM CST
                
                SQL>
                • 5. Re: Reg : DB Timezone -
                  ranit B
                  No, this is same as what Niket already suggested and i already replied once, that it is not working.

                  It is working for you most probably because either some Session variable or Database parameter is properly set.
                  • 6. Re: Reg : DB Timezone -
                    Keith Jamieson
                    Run the following on your database:
                    alter session set time_zone = 'CET';   -- central european time 
                    
                    SELECT CURRENT_TIMESTAMP FROM DUAL;
                    
                    ALTER SESSION SET TIME_ZONE = 'GMT'; -- greenwich mean time 
                    
                    SELECT CURRENT_TIMESTAMP FROM DUAL;
                    You should notice that CET is 1 hour later than GMT.

                    These are known as civilian timezones and are not part of ISO-8601
                    which is the standard which deals with timezones.

                    If you have not explicitly set a timezone and there is more than 1 timezone abbreviation that matches that timezone dffset, you will get UNK for unknown.
                    Its a bit like too many rows except its for timezones and is not an exception.

                    If you alter these at the system or database level , it will affect your database.
                    Altering them at the session level only affects that session.
                    • 7. Re: Reg : DB Timezone -
                      ranit B
                      That was nice... food for thought

                      So, now the exact issue is -
                      I have a procedure which uses this time-zone info and displays in report like 'CST' or 'IST'.
                      +2 ways+ - Either I hard-code it in procedure ,OR, use execute immediate to alter this Session variable and use it.

                      What i understood it is -
                      Using 1st way ( hard coding ) : It's always gonna show the same value, irrespective of the actual TimeZone.

                      In 2nd way : If we change the server location from Canada to Britain, only thing we need to change is the Session variable +'TIME_ZONE'+ coz the procedure will be run by a session individually.

                      Please rectify me if i'm wrong, or if you think otherwise.
                      • 8. Re: Reg : DB Timezone -
                        Keith Jamieson
                        You do neither.


                        The normal way of operating is to ensure that your database always uses the same timezone for inserting time data irrespective of its location,
                        and then the local timezone is used for displaying the time in the users local area

                        You can set the timezone for the client, so that it always shows the local time.

                        From the 11.1 Globalization Guide:

                        http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch3globenv.htm#autoId18

                        "
                        You can specify the value of NLS_TIMESTAMP_FORMAT by setting it in the initialization parameter file. You can specify its value for a client as a client environment variable.

                        You can also alter the value of NLS_TIMESTAMP_FORMAT by:

                        Changing its value in the initialization parameter file and then restarting the instance

                        Using the ALTER SESSION SET NLS_TIMESTAMP_FORMAT statement

                        "
                        • 9. Re: Reg : DB Timezone -
                          Chanchal Wankhade
                          Hi,

                          I think your second way is appropriate and looks like souts your requirement.