Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
eBoris
Member

Откуда:
Сообщений: 19
andrey_anonymous
Не понял в чем затруднение.
alter session set time_zone='UTC';
with t as (select date'2011-01-01' d from dual)
select d utc_date, d+(sysdate-current_date) local_date from t;

основное затруднение было в том, что раньше с различными time zone в Oracle никогда не работал :(

как немного смущает, что вмешиваем в сессию пользователя только для того, чтобы вычислить сдвиг с учётом time zone; в чём клиент виноват-то; а если таких преобразований несколько будет?

можно ли обойтись без alter session, если например, в строке явно укажем, что это UTC, например, '27.04.2011 12:22:58 UTC'?
(Может быть и Basil A. Sidorov подскажет)
27 апр 11, 18:29    [10576538]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18337
eBoris
как немного смущает, что вмешиваем в сессию пользователя только для того, чтобы вычислить сдвиг с учётом time zone; в чём клиент виноват-то; а если таких преобразований несколько будет?

Ну Вы бы задачу более конкретно поставили...
У Вас:
- есть данные в таблице, которые надо продвинуть на смещение sysdate
- есть некоторый техпроцесс, заносящий данные в таблицу из некоторого источника и надо делать преобразование "на лету"
- есть пользователь, который работает с системой и действия которого фиксируются приложением, которое выдает данные в UTC

Решения могут различаться.

Что до alter session - то сессия - на то и сессия, чтобы в ее пределах объяснять серверу, как конкретно желает общаться клиент. Если клиент -это отдельный техпроцесс, заносящий логи - то alter session вполне себе решение.
27 апр 11, 18:35    [10576571]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
eBoris
Member

Откуда:
Сообщений: 19
andrey_anonymous
Ну Вы бы задачу более конкретно поставили...

1. Есть таблица MsgLog, в которой столбец entry_date имеет тип Date. В этой таблице сейчас значения соответствуют временному поясу ОС сервера БД, то есть sysdate, хотя сами time zone не хранятся, поскольку столбец Date. Всё работает и здесь ничего меняться не должно.
2. Есть процедура, которой клиент сваливает набор журналов. В рамках этой процедуры происходит их разбор и добавление в таблицу MsgLog. Раньше вставлялось sysdate и всё было хорошо. Однако, если журнал приходит не сразу, то дата могла съехать, что не очень хорошо. Для предотвращения "пропусков" в журнал была добавлена дата со временем его формирования (дата текстовая в формате UTC). Теперь в разборе вместо insert into msglog(entry_date, ... ) values (sysdate, ...) требуется использовать msglog(entry_date, ...) values(to_date(str_date, 'dd.mm.yyyy hh24:mi:ss'), ...), где str_date - это Varchar2(100).
3. Если сделать как написано выше, то дата будет отличаться на несколько часов, поэтому надо эту строку str_date как-то преобразовать к Date так, чтобы не было заметно разницы с sysdate (по времени, если UTC указывает на тот же момент).

Может быть и можно сессию клиента фиксить, хотя неизвестное всё же пугает, когда не вполне очевидно...
27 апр 11, 19:00    [10576681]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18337
eBoris
2. Есть процедура, которой клиент сваливает набор журналов. В рамках этой процедуры происходит их разбор и добавление в таблицу MsgLog.
...
вместо insert into msglog(entry_date, ... ) values (sysdate, ...) требуется использовать msglog(entry_date, ...) values(to_date(str_date, 'dd.mm.yyyy hh24:mi:ss'), ...), где str_date - это Varchar2(100).
3. Если сделать как написано выше, то дата будет отличаться на несколько часов, поэтому надо эту строку str_date как-то преобразовать к Date так, чтобы не было заметно разницы с sysdate (по времени, если UTC указывает на тот же момент).


Я так понял, что в рамках сессии БД, производящей регистрацию журналов, пользователь ничем другим не занимается - типа специализированная процедура.
В этом случае выдать alter session проблем не создаст.
Тем не менее, если же загрузка журналов производится некой пакетной процедурой, то ничто не мешает в секции инициализации пакета любым способом (в т.ч. с alter seession) вычислить фактическую временную зону sysdate, сохранить в пакетной переменной и использовать полученное значение для приведения дат.
До перезагрузки сервера смещение по понятным причинам не изменится.
Саму сессию после вычисления смещения sysdate можно вернуть "на родину", тогда клиент вообще ничего не заметит.
27 апр 11, 20:10    [10576925]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Basil A. Sidorov
Member

Откуда:
Сообщений: 10750
eBoris
Извините, а к товарам в магазинах тоже придираетесь, что указывая вес они не указывают высоту над поверхностью Земли, где он был измерен и, ввиду несферичности Земли, географических координат точки измерения?
Вы не в магазине, да и в товарах меня интересует не вес, а масса.
Теперь, что касается UTC ...
Если вы начали изучать часовые пояса "только что", то я - много лет назад. Вместе датой-временем получения-отправки почты и всего такого прочего.

P.S. Предвосхищая возможные "это не Биробиджан", отвечу - часовые пояса от предметной области не зависят.
28 апр 11, 03:17    [10577975]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18482
Они от Медведева зависят
28 апр 11, 03:18    [10577976]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
eBoris,

Попробуйте:

alter session set time_zone=dbtimezone;

select dbtimezone,sessiontimezone,to_char(current_date,'hh:mi') cd,to_char(sysdate,'hh:mi') sd from dual;

drop table t purge;

create table t as select to_date('27.04.2011 12:22:58','dd.mm.yyyy hh:mi:ss') dat,cast(cast(to_timestamp_tz('27.04.2011 12:22:58 UTC','dd.mm.yyyy hh:mi:ss TZR') as timestamp with local time zone) as date) ts from dual;

select to_char(dat,'hh:mi') dat,to_char(ts,'hh:mi') ts from t;

alter session set time_zone='+2:00';

select to_char(dat,'hh:mi') dat,to_char(ts,'hh:mi') ts from t;

Но имейте ввиду - при изменении TZ базы одни и теже данные будут загружаться по разному. :)
28 апр 11, 09:16    [10578200]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18482
Есть документированный поддерживаемый способ изменить TZ базы с данными TSWLTZ ?
28 апр 11, 09:25    [10578238]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
eBoris
Member

Откуда:
Сообщений: 19
andrey_anonymous,

вариант с alter session работает, спасибо!
28 апр 11, 10:39    [10578673]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
eBoris
Member

Откуда:
Сообщений: 19
Сергей Арсеньев
eBoris,

Попробуйте:

Вот результаты:
DBTIMEZONESESSIONTIMEZONECDSD
+00:00+00:0006:3010:30


DATTS
12:2212:22


DATTS
12:2212:22


сама таблица t
DATTS
4/27/2011 12:22:58 PM4/27/2011 12:22:58 PM


Но в запросах используете dbtimezone и sessiontimezone, а sysdate к ним отношения не имеет никакого, поэтому и результат не тот, который ожидается...
28 апр 11, 10:43    [10578689]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
eBoris,

понятно, другими словами у Вас в БД установлена не та TZ (0), которая установлена в операционке на сервере где крутится БД (+4)?
28 апр 11, 10:53    [10578745]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
eBoris
Member

Откуда:
Сообщений: 19
Basil A. Sidorov
eBoris
Извините, а к товарам в магазинах тоже придираетесь, что указывая вес они не указывают высоту над поверхностью Земли, где он был измерен и, ввиду несферичности Земли, географических координат точки измерения?
Вы не в магазине, да и в товарах меня интересует не вес, а масса.

Масса многих интересует, но либо возвращаемся к весу, поскольку "Весы́ — устройство или прибор для определения массы тел (взвешивания) по действующему на них весу, приближённо считая его равным силе тяжести", либо объясните, какую массу (гравитационную или инертную) имеете в виду и, главное, как её измеряете.

Basil A. Sidorov
Теперь, что касается UTC ...
Если вы начали изучать часовые пояса "только что", то я - много лет назад. Вместе датой-временем получения-отправки почты и всего такого прочего.


Поскольку
http://www.gramota.ru/spravka/letters/?rub=kav
В каких случаях ставятся кавычки?
В современном русском языке кавычки выполняют следующие функции:
1. Выделение безабзацной прямой речи и цитат.
2. Выделение условных (собственных) наименований.
3. Выделение слов, которые употребляются в необычном, ироническом, особом значении.
,
а ни в одном своём сообщении (данной темы) никто не употреблял словосочетание "только что", то первые два пункта отпадают. Можете пояснить, какой именно смысл вкладывали при выделении кавычками?

P.S. Я писал, что в Oracle раньше не сталкивался с time zone, но примеров в жизни было много: и конференции срывались, так как время каждый в своём временном поясе указывал, и летом GMT+3 в качестве time zone указывали...

P.P.S. Предвосхищая возможные измерения и снятия мерок сразу скажу, что вы опытнее, сильнее, красивее и чего угодно ещё... Но я всё равно счастлив!
28 апр 11, 11:16    [10578905]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
eBoris
Member

Откуда:
Сообщений: 19
Сергей Арсеньев
eBoris,

понятно, другими словами у Вас в БД установлена не та TZ (0), которая установлена в операционке на сервере где крутится БД (+4)?


У Oracle тоже написано "The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle Corporation 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."
28 апр 11, 11:19    [10578933]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
eBoris
to avoid data conversion and improve performance

Аналогично и с кодовой страницей на клиенте и сервере. Если одинаковые - все хорошо, и если начинаете обманывать, то вроде не замечает, пока кто-нибудь не попросит в другой кодовой странице - вот тут и идут знаки вопроса.

Другими словами:
alter session set time_zone = 'UTC';

select round((sysdate-current_date)*96)/96 from dual;
и даст Вам нужную поправку для конкретного сервера.
28 апр 11, 11:37    [10579095]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
ShooKhan
Member

Откуда: Минск
Сообщений: 24
К сожалению, прочитав все сообщения несколько раз, так и не нашел ответа на исходный вопрос.
Как вычислить поправку для тех дат, что уже были или еще будут? Как учесть переход на зимнее/летнее время? Dbtimezone, как я понимаю, вычисляет это на лету только для текущего времени.

Конкретно у меня задача - на ходу менять unixtimestamp на локальное время сервера и обратно. Процедура, которая будет этим заниматься, должна работать на любом сервере в любом часовом поясе. Пока что решение видится в том, что я при установке процедуры определяю какое сейчас время - летнее или зимнее, смотрю разницу по dbtimezone, фиксирую один сдвиг, добавляю или убавляю час и фиксирую второй сдвиг. Ну а если перехода нет (не будет), то местный интегратор должен руками это поправить.

Можете что-то предложить еще?
Спасибо.
11 май 11, 14:16    [10635351]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
ShooKhan,

В принципе, если точность времени важна на столько, что надо отличать одинаковое время (до и после перевода часов), то лучше хранить его в соответствующем timestamp, а не в неспособном к этому типе data и быть более аккуратным в преобразованиях дат и пр.
11 май 11, 15:12    [10635943]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Basil A. Sidorov
Member

Откуда:
Сообщений: 10750
ShooKhan
Конкретно у меня задача - на ходу менять unixtimestamp на локальное время сервера и обратно. Процедура, которая будет этим заниматься, должна работать на любом сервере в любом часовом поясе.
unixtime - интервал в секундах от '1970-01-01 00:00:00 UTC' поэтому, вроде как, должно работать:
to_date('1970-01-01 00:00:00 00:00', 'yyyy-mm-dd hh24:mi:ss tzh:tzm') + unixtime/86400 from dual;
"Как-то так" (ц) Винни-Пух.
11 май 11, 15:45    [10636327]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
ShooKhan
Member

Откуда: Минск
Сообщений: 24
Basil A. Sidorov
unixtime - интервал в секундах от '1970-01-01 00:00:00 UTC' поэтому, вроде как, должно работать:
to_date('1970-01-01 00:00:00 00:00', 'yyyy-mm-dd hh24:mi:ss tzh:tzm') + unixtime/86400 from dual;
"Как-то так" (ц) Винни-Пух.


Мне не нужен date в UTC, мне нужен локальный date, то есть с учетом time zone и day light saving.
11 май 11, 16:39    [10636908]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Basil A. Sidorov
Member

Откуда:
Сообщений: 10750
ShooKhan
Мне не нужен date в UTC, мне нужен локальный date, то есть с учетом time zone и day light saving.
"Да ё-моё! А я за что её держал?!" (ц) Кузьмич, "Особенности национальной рыбалки".
Часовой пояс указан только для того, чтобы точка отсчёта была "откуда нужно". От 1970-01-01 00:00:00 UTC, а не от 1970-01-01 00:00:00 местного времени.

P.S. Ну нет у меня Oracle дома, но неужели так сложно сделать пару запросов на (вашем) стенде?
11 май 11, 16:53    [10637039]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
ShooKhan,

А в Oracle date и не содержит временную зону. :)
+ результат выполнения to_date
ORA-01821: формат даты не распознан
Если не брать в расчет вариант ТС у которого сильно распределенная база данных по часовым поясам, в которой с целью оптимизации БД считают время в одной ТЗ, но информацию надо приводить ко времени, которая установлена в операционке сервера (причем см. выше они все разные :) ), то можно что-то типа:
select to_char(cast(to_timestamp_TZ('1970-01-01 00:00:00 00:00', 'yyyy-mm-dd hh24:mi:ss tzh:tzm') as timestamp with local time zone)+1/86400,'dd.mm.yyyy hh24:mi:ss') from dual;
Если же время в БД рассогласовано со временем в операционке, то нужно просто вычислить этот поправочных коэффициент и использовать.
11 май 11, 16:55    [10637058]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
ShooKhan
Member

Откуда: Минск
Сообщений: 24
Basil A. Sidorov
Часовой пояс указан только для того, чтобы точка отсчёта была "откуда нужно". От 1970-01-01 00:00:00 UTC, а не от 1970-01-01 00:00:00 местного времени.

P.S. Ну нет у меня Oracle дома, но неужели так сложно сделать пару запросов на (вашем) стенде?


Да хоть десять в разных часовых поясах в разных странах. Но похоже мы на разных языках говорим. Одно и то же значение времени в UTC будет соответствовать разным значениям локального времени в разных time zone. Где это учтено в Вашей формуле?
11 май 11, 17:17    [10637241]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Basil A. Sidorov
Member

Откуда:
Сообщений: 10750
ShooKhan
Одно и то же значение времени в UTC будет соответствовать разным значениям локального времени в разных time zone. Где это учтено в Вашей формуле?
Вы определение unixtime помните? Число секунд от заданного "начала времён".
Точка отсчёта - задана и, вне зависимости от того, проведёт ли Oracle все вычисления в UTC или сначала приведёт '1970-01-01 00:00:00 UTC' к локальному времени, а потом начнёт прибавлять дни и секунды - итоговый результат будет правильным.
11 май 11, 17:23    [10637292]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Basil A. Sidorov
Member

Откуда:
Сообщений: 10750
Сергей Арсеньев
А в Oracle date и не содержит временную зону
Ну значит to_timestamp_tz.
Согласно документации - приводить его к дате необязательно :)
11 май 11, 17:25    [10637308]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
ShooKhan
Member

Откуда: Минск
Сообщений: 24
Сергей Арсеньев
Нужно просто вычислить этот поправочных коэффициент и использовать.

Вот в этом-то и корень проблемы. Вычислить я могу только в текущем времени, то есть сейчас я получу сдвиг для летнего времени, а мне нужно сейчас конвертировать как летние, так и зимние даты.
11 май 11, 17:32    [10637356]     Ответить | Цитировать Сообщить модератору
 Re: перевести дату в UTC ко времени ОС dbms (часовой пояс sysdate)  [new]
Сергей Арсеньев
Member

Откуда:
Сообщений: 4118
ShooKhan,

У Вас тоже БД работает в другом часовом поясе чем операционная система и Вам надо знать время в терминах операционной системы?
11 май 11, 17:35    [10637373]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Oracle Ответить