Dbtimezone oracle на что влияет
На одном из серверов базы данных Oracle отображается «+01:00», когда я запускаю «Выбрать dbtimezone из двойного», означает ли это, что летом часы перейдут на один час вперед? На другом сервере отображается «+00:00». Означает ли это, что настройка сервера базы данных — GMT? но я использую sysdate в оракуле pl/sql. Клиент говорит, что сервер Aix находится в режиме летнего времени, значит ли это, что сервер БД примет настройку сервера AIX после смены часов? Как решить эту проблему.
Ответ: это зависит.
Всего в вашей базе данных три часовых пояса
Это вы можете изменить ALTER SESSION SET TIME_ZONE=. в любое время. Актуально для результата
Это также целевой часовой пояс, когда вы делаете CAST( AS TIMESTAMP WITH TIME ZONE)
Значение по умолчанию SESSIONTIMEZONE может быть установлено переменной среды ORA_SDTZ или (в Windows) записью реестра HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\ORA_SDTZ (для 32-разрядного клиента), соответственно. HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\ORA_SDTZ (для 64-битного клиента).
На самом деле это не так важно в повседневном использовании, это актуально только для TIMESTAMP WITH LOCAL TIME ZONE столбцов типа данных и определяет формат хранения.
Это НЕ часовой пояс SYSDATE или SYSTIMESTAMP .
Вы не можете изменить DBTIMEZONE свою базу данных, если база данных содержит таблицу со TIMESTAMP WITH LOCAL TIME ZONE столбцом, а столбец содержит данные. В противном случае его можно изменить с помощью ALTER DATABASE SET TIME_ZONE='. '; . Изменение не вступит в силу, пока база данных не будет закрыта и перезапущена.
DBTIMEZONE устанавливается при создании базы данных. Если при создании базы данных часовой пояс не указан, Oracle по умолчанию использует часовой пояс операционной системы сервера.
- Часовой пояс операционной системы сервера баз данных:
Этот часовой пояс актуален для результата
Естественно, этот часовой пояс нельзя изменить на уровне базы данных. Если в вашей стране используется летнее время, этот часовой пояс может меняться два раза в год. Вы можете опросить его SELECT TO_CHAR(SYSTIMESTAMP, 'tzr') FROM dual; , например, с помощью .
Итак, если ваша ОС сервера БД настроена правильно, вы должны получить летнее время со следующей недели (по крайней мере, для Европы).
Oracle предоставляет пользователю возможность работать с данными в различных часовых поясах. Выбор функций или переменных для совершения таких операций в каждом конкретном случае может быть не всегда очевиден.
В данной статье приведена информация по теме работы с текущим временем и датами в различных временных зонах.
В базе данных Oracle имеются встроенные функции работы со временем в следующих часовых поясах.
1. Часовой пояс сессии/клиента
Текущее значение часового пояса сессии/клиента содержится в SESSIONTIMEZONE.
Это значение может быть изменено командой ниже.
В качестве SESSIONTIMEZONE может быть установлено одно из следующих значений:
- Значение временной зоны ОС ('OS_TZ')
- Значение временной зоны базы данных ('DB_TZ')
- Смещение временной зоны от UTC (например, '-05:00')
- Имя временной зоны (например, 'Europe/Minsk')
Для получения текущего времени в часовом поясе сессии используются переменные CURRENT_DATE, LOCALTIMESTAMP, CURRENT_TIMESTAMP.
Они отличаются друг от друга возвращаемым типом:
CURRENT_DATE возвращает DATE, LOCALTIMESTAMP - TIMESTAMP и CURRENT_TIMESTAMP - TIMESTAMP WITH TIME ZONE
2. Часовой пояс базы данных
Текущее значение часового пояса базы данных содержится в DBTIMEZONE.
Это значение используется для внутреннего хранения полей типа TIMESTAMP WITH LOCAL TIME ZONE. Такие поля преобразуются в/из часового пояса сессии при select/insert действиях, поэтому на самом деле значение DBTIMEZONE не имеет большой важности.
Это не часовой пояс переменных SYSDATE и SYSTIMESTAMP.
3. Часовой пояс операционной системы базы данных
Можно посмотреть используя переменную TZR.
Для получения текущего времени в этом часовом поясе используются переменные SYSDATE и SYSTIMESTAMP.
4. Часовой пояс UTC
В Oracle существет специальная функция sys_extract_utc для преобразования любого значения таймстампа в часовой пояс UTC. Ниже приведены разные варианты её использования.
It seems to think the database time zone is GMT, yet the SYSDATE is the same as the CURRENT_DATE.
When I remote into that server (Windows), the time zone is apparently CST (however, I am aware that this could be picking up my Terminal Services Client Time Zone Offset, but this machine doesn't have Terminal Services on it, just administrative)
Running the same thing against a server in Amsterdam (4 minutes later all from the same TOAD client), I'm getting:
Note the +2, but at least the SYSDATE and CURRENT_DATE are differing.
What is going on here? Where does SYSDATE come from and is there anything else which affects it?
It seems like DBTIMEZONE is not used for any of these things? So what is DBTIMEZONE used for?
3 Answers 3
There are actually 3 timezones here, not 2
- the timezone of the session/client
- Shown in SESSIONTIMEZONE
- This is the timezone of CURRENT_DATE, LOCALTIMESTAMP and CURRENT_TIMESTAMP. The difference between those 3 is the return type, they return a DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE respectively)
- Shown in DBTIMEZONE
- This is the the timezone used for the internal storage of TIMESTAMP WITH LOCAL TIME ZONE values. Note that values are converted to/from session timezone on insert/select so it actually isn't as important as it seems
- This is NOT the timezone of SYSDATE/SYSTIMESTAMP
- In unix, it is based on the TZ variable when Oracle is started
- This is the timezone of SYSDATE and SYSTIMESTAMP
In your first example, I can see that the session TZ is UTC-6, the database TZ is UTC, and the database OS timezone is UTC-6.
In your second example, I can see that the session TZ is UTC-6, the database TZ is UTC+2, and the database OS timezone is UTC+1.
The details are in the fine print of the documentation. Take a look at the Return Type, and the actual Timezone the DATE or TIMESTAMP is calculated in.
- SYSDATE
- Return Type: DATE
- Time Zone: Host OS of Database Server
- CURRENT_DATE
- Return Type: DATE
- Time Zone: Session
- SYSTIMESTAMP
- Return Type: TIMESTAMP WITH TIME ZONE
- Time Zone: Host OS of Database Server
- CURRENT_SYSTIMESTAMP
- Return Type: TIMESTAMP WITH TIME ZONE
- Time Zone: Session
- LOCALTIMESTAMP
- Return Type: TIMESTAMP
- Time Zone: Session
- DBTIMEZONE
- Time Zone: DB Time Zone. Inherits from DB Server OS, but can be overridden using set at DB Creation or Alter using TIME_ZONE DB Parameter (SET TIME_ZONE=. ). This affects the time zone used for TIMESTAMP WITH LOCAL TIME ZONE datataypes.
- SESSIONTIMEZONE
- Time Zone: Session Timezone. Inherits from Session hosting OS, but can be overridden using ALTER SESSION (ALTER SESSION SET TIME_ZONE=. ).
Return Type, indicates whether or not the Timezone is available within the Datatype. If you try to print TZR if datatype does not carry TimeZone, then it will just show up as +00:00 (doesn't mean it is GMT). Otherwise It will show the TimeZone matching either the Database or Session as indicated.
Time Zone, indicates in which Timezone the time is calculated. For matching TimeZone, the same Date/Time will be shown (HH24:MI).
Note that none of the FUNCTIONS return TIME in the Time Zone set with the DB TIME_ZONE (or as returned by the DBTIMEZONE function). That is, none of the functions also return a datatype of TIMESTAMP WITH LOCAL TIME ZONE. Howver you can convert the output of any of the functions that does return a timezone into a different timezone (including DBTIMEZONE) as follows:
Why you should not set the DBTIMEZONE to a “Location Time Zone” format in Oracle.
First of all, the DBTIMEZONE does not control at what time scheduler jobs exactly starts. When I have been taking care of my scheduler jobs recently I was wondering how do I now configure these, I came across a very simple question. After what time zone will “by hour 23” executed which I have planned here? In addition to the well known SYSDATE, I came across the DBTIMZONE parameter. What would make more sense that the database time zone controls when my >database! < scheduler jobs runs? Then it is obvious that I have to take care about summertime or daylight saving time (DST) in my case ‘Europe/Berlin’. Seems to be right . … I thought … NOT – I was wrong!
The first thing was to determine the actual database time zone setting:Next step: Simply switching the database time zone. Please, don’t do what I was trying to do (‘Europe/Berlin’)!
Thanks to the error message, I came across the true purpose of the DBTIMEZONE. „The only function of the database time zone is that it functions as a time zone in which the values of the “TIMESTAMP WITH LOCAL TIME ZONE” (TSLTZ) datatype are normalized to the current database timezone when they are stored in the database. However, these stored values are always converted into the session time zone on insert and retrieval, so the actual setting of the database time zone is more or less immaterial.“ Thus it is at first not as important as it sounds.
However, you should not set the DBTIMEZONE to a time zone that is affected by daylight saving time (DST). Thus an „OFFSET“ +00:00 ( -07:00, +02:00) or a static time zone like UTC or GMT that is not affected by summertime is recommended. The best setting would simply be +00:00. If you have an OFFSET +02:00 or similar, which in doubt was set through the CREATE DATABASE, you can leave it. In my case above (+02:00) everything is fine.
For the actual implementation a restart of the database is required. Do you want to change the DBTIMEZONE anyway or really have a “false“ value, a location time zone with DST and get the same error message like me, you first have to locate the tables that uses the data type LOCAL TIME ZONE WITH LOCAL TIMESTAMP:
In the second step you should export the appropriate tables and drop them. Set DBTIMEZONE, bounce the database, restart and import the tables. If you don’t want to drop the tables, because it’s only a sample schema you could alternatively set the relevant column to DATE and afterwards back to TIMESTAMP(6) WITH LOCAL TIME ZONE. But caution, information will be lost with the last alternative.
And what about my scheduler jobs now? They are controled by the time set on my server. If you have problems that your database jobs do not start when they are supposed to, you should verify your server settings and OS time:
Check OS time from the database by using SYSTIMESTAMP:
Finally, an interesting document that deals with topic date and time in the Oracle database at MOS: Doc ID 340512.1
Johannes Ahrends
Oracle ACE and specialist for topics like HA, migration, Standard Edition, backup and more
more about
Johannes AhrendsПару лет назад создавался сервер Oracle 11.2.0.4 для клиента, расположенного на Украине. Соответственно, часовой пояс сервера Windows — Киев.
А параметр DBTIMEZONE, насколько я заметил за пару лет наблюдения, меняется, летом это "+03:00", зимой "+02:00".Там написано
The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle recommends that you set the database time zone to UTC (0:00) to avoid data conversion and improve performance when data is transferred among databases. This is especially important for distributed databases, replication, and exporting and importing.Без выполнения рекомендаций этой статьи, при обновлении файла timezlrgXX.dat значения в полях TSLTZ "поедут" на пару часов вперёд-назад, особенно для российских часовых поясов.
Гугление показывает, что единственным путём изменение DBTIMEZONE является экспорт таблиц с полями TSLTZ, их удаление, и последующий импорт. Но при этом значение опять же таки "поедут".
Прямая команда предсказуемо отказывается выполняться
SQL> alter database set TIME_ZONE='GMT';
alter database set TIME_ZONE='GMT'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columnsЭто и хорошо, иначе все TSLTZ поля были бы испорченными
Будем делать по своему.
Что нам может здесь помешать:
1) Участие полей типа TSLTZ первичных ключах.
Найти такие таблицы и поля можно с помощью запроса
select /*+ no_query_transformation */ a. CONSTRAINT_TYPE , a. OWNER , a. TABLE_NAME , c . COLUMN_NAME , a. OWNER , a. CONSTRAINT_NAME
and b . TABLE_OWNER = c . OWNER and b . TABLE_NAME = c . TABLE_NAME and b . COLUMN_NAME = c . COLUMN_NAME
В рамках процедуры апдейта мы будем некоторые поля ставить в значения NULL, а для первичных ключей это недопустимо.
Такие ключи или IOT-таблицы придётся пересоздавать, со всеми констрейнтами.
Я рекомендую IOT-таблицы преобразовать в обычные HEAP, чтобы для них корректно прошла процедура трансляции, описанная ниже, а по завершении вернуть таблицы к виду IOT.Этот запрос отображает также констрейнты UNIQUE.
Для UNIQUE установка в NULL допустима, если на них не ссылаются никакие FK. Проверьте это самиЭтот вопрос должен быть решен вами самостоятельно ДО выполнения данной процедуры
Читайте также: