Time Zone
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Time Zone

  1. #1
    Join Date
    Jul 2002
    Posts
    2
    Hi

    Our customer in Japan has thier UNIX machine set to dallas time. However they would
    like to set to Japanese time in the oracle database.
    Any idea??

    MK

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    1. Shutdown the database.
    2. Change time from the OS
    3. startup database
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    The session time zone can be set to:
    - O/S local time zone
    - Database time zone
    - An absolute offset
    - A named region

    1. The first method consists to use one of the following ALTER SESSION SET
    TIME_ZONE statements:
    SQL> ALTER SESSION SET TIME_ZONE = local;
    SQL> ALTER SESSION SET TIME_ZONE = dbtimezone;
    SQL> ALTER SESSION SET TIME_ZONE = '-05:00';
    SQL> ALTER SESSION SET TIME_ZONE = 'Europe/London';

    2. As an alternative, the operating system environment variable ORA_SDTZ can
    also be used to set the session time zone:
    ORA_SDTZ = 'OS_TZ' | 'DB_TZ' | '[+ | -] hh:mm' | 'timezone_region'

    Example:
    $ ORA_SDTZ='OS_TZ'
    $ export ORA_SDTZ

    $ ORA_SDTZ='-05:00'
    $ export ORA_SDTZ

  4. #4
    Join Date
    Jul 2002
    Posts
    2
    Stecal,

    Is this a 8i or 9i feature

    MK

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    There a couple of things that are new for 9i, but select sessiontimezone from dual, for example, works in both.

    4. How to set the database time zone ?
    ======================================
    Answer
    ------
    Note that the database time zone is only relevant for TIMESTAMP WITH LOCAL TIME ZONE
    columns.

    1. At creation time
    SQL> CREATE DATABASE ...
    SET TIME_ZONE='Europe/London';
    If not specified with the CREATE DATABASE statement, the database time zone
    defaults to the serverís O/S timezone offset.

    2. After database creation, use the ALTER DATABASE SET TIME_ZONE statement and
    then shut down and restart the database.
    SQL> ALTER DATABASE SET TIME_ZONE = '-05:00';
    SQL> ALTER DATABASE SET TIME_ZONE = 'Europe/Lisbon';
    The change will not take effect until the database is bounced.


    5. How to list the valid time zone regions ?
    ============================================
    Answer
    ------
    SQL> SELECT * FROM v$timezone_names;

    TZNAME TZABBREV
    ------------------------------ ----------
    Pacific/Tahiti LMT
    Poland LMT
    US/Pacific PST
    Europe/Zurich CET
    ...

    The result will depend on which timezone file is currently in use.
    See Q&A 7-8 for further details.


    6. How to retrieve the time zone offset corresponding to a time zone region ?
    =============================================================================
    The new 9i TZ_OFFSET() SQL function returns the time zone offset displacement
    to the input time zone region.

    SQL> SELECT TZ_OFFSET('Europe/London') FROM DUAL;

    TZ_OFFS
    -------
    +01:00

    The returned offset depends on the date this statement is executed.
    For example, in the 'US/Pacific' time zone, it may return '-07:00' or '-08:00'
    whether daylight saving is in effect or not.

    SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;
    returns the time zone offset corresponding to the time zone set for session
    and database.

    TZ_OFFS TZ_OFFS
    ------- -------
    +01:00 +00:00

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width