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
Printable View
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
1. Shutdown the database.
2. Change time from the OS
3. startup database
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
Stecal,
Is this a 8i or 9i feature
MK
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