This discussion is archived
9 Replies Latest reply: Jan 3, 2013 4:10 AM by Chanchal Wankhade RSS

Reg : DB Timezone -

ranit B Expert
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 -
    KeithJamieson Expert
    Currently Being Moderated
    I did a quick google and found this


    Get time zone abbreviations
  • 3. Re: Reg : DB Timezone -
    ranit B Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 -
    KeithJamieson Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 -
    KeithJamieson Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Hi,

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points