Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 преобразовать datetime к decimal или наоборот с учетом зоны  [new]
alex alex
Member

Откуда:
Сообщений: 25
ситуация: требуется сравнить два поля со временем. в одном оно записано в decimal формате - число миллисекунд от 1-го января 1970 по GMT. во втором - datetime. также известен часовой пояс, в котором отсчитывался этот datetime. Как их сравнить, те узнать что было раньше или позже? Заранее большое спасибо, уважаемые эксперты.
Да, используется SQL7 сравнивать надо во view или запросе.
1 окт 04, 19:06    [1003372]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
second*1000
Guest
declare @diff1 bigint, @diff2 bigint, @tzone int
set @tzone = -3
--для примера вычислим оба, хотя одно значение хранится в БД
set @diff1=cast(DATEDIFF(second,cast('19700101' as datetime),GETUTCDATE()) as bigint)*1000
set @diff2=cast(DATEDIFF(second,cast('19700101' as datetime),DATEADD(Hour,@tzone,getdate())) as bigint)*1000
select @diff1-@diff2
--конструкция as bigint)*1000 - чтобы не было
Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.
1 окт 04, 20:05    [1003452]     Ответить | Цитировать Сообщить модератору
 Этот вариант не учитывает переходов на летнее+  [new]
alex alex
Member

Откуда:
Сообщений: 25
время. Как бы еще и этот вопрос обойти?
Спасибо за ответ.
2 окт 04, 11:08    [1003870]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
qu3
Guest
second*1000 - будут съедаться миллисекунды, кстати, а они нужны в задаче?
ихмно, зная только часовой пояс, невозможно знать достоверно о переходе на летнее время - определяется не географами а законами, которые в разных странах разные ;-)
2 окт 04, 14:31    [1004001]     Ответить | Цитировать Сообщить модератору
 именно  [new]
alex alex
Member

Откуда:
Сообщений: 25
именно есть московское время, есть другие... дык в sql можно этот вопрос решить? в крайнем случае готов набить таблицу, когда нужная временная зона переходит на летнее на несколько лет вперет. в каком формате набивать, если прокатит, как изменится запрос?
большое спасибо за помощь
3 окт 04, 00:08    [1004366]     Ответить | Цитировать Сообщить модератору
 Насчет миллисекунд  [new]
alex alex
Member

Откуда:
Сообщений: 25
Миллисекунды тоже бы надо учитывать.
4 окт 04, 14:39    [1006490]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
qu3
Guest
>если прокатит, как изменится запрос?
ну, наверное вот в месте приведения к Гринвичу локального времени
DATEADD(Hour,@tzone+case when localtime <в летнем времени> then 1 else 0 end ,localtime) ?
а по поводу милисекунд - так разбить "число миллисекунд от 1-го января 1970" на целое число секунд + остаток милисекунд, ну и добавлять DATEDIFF-ом один раз секунды и еще раз ms, ihmo.
4 окт 04, 22:27    [1007902]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
Crimean
Member

Откуда:
Сообщений: 13148
А почему не сравнить dateadd( ms, @Decimal, '01/01/1970' ) с dateadd( hour, @Часовой пояс, @Date )? Задача-то решается вчистую в datetime виде... Или я чего не понимаю?
5 окт 04, 11:13    [1008629]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
quest00x
Guest
2 Crimean
select dateadd(ms, 1096960991000, '01/01/1970') 
Arithmetic overflow error converting expression to data type int.
Слишком далек 70-й год, однако. Впрочем, и задачу то трудно представить где нужно мерить миллисекунды с тех пор... Астрономия ? История ? :-))
5 окт 04, 11:29    [1008723]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Эх, молодешшшшшь.....

declare @n decimal
select @n = 1096960991000
-- select 24 * 60 * 60 * 1000 = 86400000
select dateadd( ms, @n - floor( @n / 86400000 ) * 86400000, dateadd( day, floor( @n / 86400000 ), '01/01/1970' ))
5 окт 04, 11:44    [1008782]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
quest00x
Guest
>Эх, молодешшшшшь.....
Дык, в посте qu3:
"а по поводу милисекунд - так разбить "число миллисекунд от 1-го января 1970" на целое число секунд + остаток милисекунд"
А ежели все разжевывать - не вырастут у молодых зубы, ихмно :-))
5 окт 04, 12:02    [1008868]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Дык "разбиение" еще сделать корректно надо. С тем же floor, скажем, ибо % с decimal не работает. Ну и сутки всяко лучше, чем часы. За 100 лет суток-то будет ~ 100 * 365 - фигня для int . И ms останется не более чем 24 * 60 * 60 * 1000 . Я к тому, что вариант решения должен быть контролируемый в обозримом диапазоне возможных дат :)
5 окт 04, 12:55    [1009128]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
alex alex
Member

Откуда:
Сообщений: 25
Всем огромное спасибо за обсуждение.
1.Я правильно понимаю, что результат datediff(second,'19700101',@datetime) не зависит от timezone-ы сервера?

2. Timezone-а учитывается при записи в базу и выводе?

Если так, то достаточно сравнивать р-т diff и decimal?Т.е. поправки на стороне sql вообще не нужны? или все не так?

NB запись производит application server, а ему дается просто long в обоих случаях.
5 окт 04, 17:22    [1010415]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
guest00x
Guest
1. Правильно.
2. Чего и кем? Самим SQL сервером? Если это значение функции getdate() - то да, иначе - нет.
5 окт 04, 17:43    [1010479]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
alex alex
Member

Откуда:
Сообщений: 25
автор
1.Я правильно понимаю, что результат datediff(second,'19700101',@datetime) не зависит от timezone-ы сервера?

Или, если и зависит, то по крайней мере он одинаковый для одной timezone-ы и летом и зимой даже в случае перехода с/на летнее время?
5 окт 04, 17:45    [1010496]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Как результат выполнения выражения типа "2-1" может зависеть от чего либо?! :O :D
5 окт 04, 18:09    [1010569]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
alex alex
Member

Откуда:
Сообщений: 25
выражение <19700101> означает 19700101 по GMT, MSC или что?
от этого зависит р-т datediff
5 окт 04, 18:19    [1010603]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Результат datediff точно не зависит. А вот результат преобразования строки в datetime...

BOL, XPath Data Types
The date and time conversions are designed to work whether the value is stored in the database using the SQL Server datetime data type or a string. Note that the SQL Server datetime data type does not use timezone and has a smaller precision than the XML time data type. To include the timezone data type or additional precision, store the data in SQL Server 2000 using a string type.
5 окт 04, 18:31    [1010643]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
alex alex
Member

Откуда:
Сообщений: 25
Спасибо большое, дык не подскажете, как преобразуется данная стринга к datetime в sql7?
5 окт 04, 18:40    [1010670]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
Crimean
Member

Откуда:
Сообщений: 13148
convert
5 окт 04, 20:06    [1010845]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
alex alex
Member

Откуда:
Сообщений: 25
Простите, если выгляжу слишком назойливым или тупым, но к какой timezone-е sql server отнесет данную строку при конвертации в datetime? Или его вообще зона не волнует, тогда, предположим, сегодня ночью часы переводились на час вперед, я засылаю строку 041006-12:10 преобразованную к datetime, а потом прошу datediff c 19700101. Что ответит datediff в миллисекундах - кол-во ms от 19700101 по GMT до 041006-12:10 по GMT или до 041006-11:10 по GMT или до 041006-13:10 по GMT?
Поясните, пожалуйста.
6 окт 04, 12:26    [1012128]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
Glory
Member

Откуда:
Сообщений: 104760
но к какой timezone-е sql server отнесет данную строку при конвертации в datetime?
Разумеется к той, в которой он находится сам. Вернее к той что приписана в установках операционной системы. У типа данных datetime нет никаких признаков отвечающих за timezone

Что ответит datediff в миллисекундах - кол-во ms от 19700101 по GMT до 041006-12:10 по GMT или до 041006-11:10 по GMT или до 041006-13:10 по GMT?

datediff вернет разницу между двумя датами на текущем серевере. Потому, что у типа данных datetime нет никаких признаков отвечающих за timezone.
6 окт 04, 12:53    [1012293]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
alex alex
Member

Откуда:
Сообщений: 25
автор
Разумеется к той, в которой он находится сам.

Как тогда объяснить рез-ты такого эксперимента? Записываем в поле datetime 2004-10-06 14:47:38.217 в decimal 1097059658218 - ms c 19700101 GMT до 2004-10-06 14:47:38.217 MSC. Записываем в поле datetime 2004-12-06 15:03:52.570 в decimal 1102334632569 - ms c 19700101 GMT до 2004-12-06 15:03:52.570 MSC.

Делаем select datediff(second,'19700101',logdate) - trancacttime/1000
Получаем
14399.782000
10799.431000

Разница ровно час. Если бы сервер производил вычисления отталкиваясь от некой "своей" timezon-ы, разницы бы не было. Я не прав?
6 окт 04, 15:29    [1013239]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
Glory
Member

Откуда:
Сообщений: 104760
alex alex

Как тогда объяснить рез-ты такого эксперимента? Записываем в поле datetime 2004-10-06 14:47:38.217 в decimal 1097059658218 - ms c 19700101 GMT до 2004-10-06 14:47:38.217 MSC. Записываем в поле datetime 2004-12-06 15:03:52.570 в decimal 1102334632569 - ms c 19700101 GMT до 2004-12-06 15:03:52.570 MSC.


Объясните сначала отчего вы считаете что ваши числа соответсвуют именно тем датам которые вы декларируете. Ибо разницами между датами как датами у меня лично не совпадает с разницем между числами.

declare @x1 decimal(18,0), @x2 decimal(18,0)
declare @d1 datetime, @d2 datetime
set @x1 = 1097059658218
set @x2 = 1102334632569
set @d1 = '2004-10-06 14:47:38.217'
set @d2 = '2004-12-06 15:03:52.570'

select datediff(s, @d1, @d2) as delta_sec, (@x2-@x1)/1000.00
6 окт 04, 15:45    [1013319]     Ответить | Цитировать Сообщить модератору
 Re: преобразовать datetime к decimal или наоборот с учетом зоны  [new]
Glory
Member

Откуда:
Сообщений: 104760
declare @x1 decimal(18,0), @x2 decimal(18,0)
declare @d1 datetime, @d2 datetime
set @x1 = 1097059658218
set @x2 = 1102334632569
set @d1 = '2004-10-06 14:47:38.217'
set @d2 = '2004-12-06 15:03:52.570'

select dateadd(ms, msec, dateadd(ss, sec, '19700101'))
from 	(select @x1-sec*1000 as msec, sec 
	from	(select round(@x1/1000,0,1) as sec
		) as a
	) as a
select dateadd(ms, msec, dateadd(ss, sec, '19700101'))
from 	(select @x2-sec*1000 as msec, sec 
	from	(select round(@x2/1000,0,1) as sec
		) as a
	) as a
6 окт 04, 15:49    [1013335]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить