Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Хранение отрезка времени в часовом формате  [new]
ПаWWWлОдАрЕц
Member

Откуда: NSK-PVL
Сообщений: 135
Все привет уважаемые. Собственно сам вопрос:
Необходимо сделать экспорт данных в базу из файла экселя (скрин прилагаю). Суть в том, что там данные по моточасам хранитяся в виде 39:28:12, т.е. типа 39 ч. 28 мин. 12 сек. .Хотелось бы не танцевать с бубнами, а воспользоваться штатными средствами по экспорту/импорту данных (старый добрый DTS) что бы перегнать это все в базу. Так вот, какой ??? тип данных выбрать для столбца в таблице при настройке во время экспорта, если в нем необходимо хранить данные о количестве затраченного времени в часовом выражении причем количество часов может быть гораздо более 24:00:00, что бы в дальнейшем еще и запросами можно было суммировать эти часы по объекту (ну скажем за месяц, год и т.д.) Непонятку вызвали значения отмеченные красным цветом в ячейках.

select @@version =
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
Apr 2 2010 15:53:02
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
12 июн 12, 16:15    [12702943]     Ответить | Цитировать Сообщить модератору
 Re: Хранение отрезка времени в часовом формате  [new]
ПаWWWлОдАрЕц
Member

Откуда: NSK-PVL
Сообщений: 135
Сам файл

К сообщению приложен файл. Размер - 118Kb
12 июн 12, 16:33    [12703041]     Ответить | Цитировать Сообщить модератору
 Re: Хранение отрезка времени в часовом формате  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Раз данные с точностью до секунды, то поле типа int и хранить в секундах. Загружать как текст парсить, и конвретировать в int.

Сообщение было отредактировано: 12 июн 12, 16:43
12 июн 12, 16:43    [12703110]     Ответить | Цитировать Сообщить модератору
 Re: Хранение отрезка времени в часовом формате  [new]
ПаWWWлОдАрЕц
Member

Откуда: NSK-PVL
Сообщений: 135
pkarklin
Раз данные с точностью до секунды, то поле типа int и хранить в секундах. Загружать как текст парсить, и конвретировать в int.


"Хотелось бы не танцевать с бубнами."

Есть еще варианты?
12 июн 12, 19:31    [12704060]     Ответить | Цитировать Сообщить модератору
 Re: Хранение отрезка времени в часовом формате  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
ПаWWWлОдАрЕц
"Хотелось бы не танцевать с бубнами."


А придется...
12 июн 12, 19:42    [12704123]     Ответить | Цитировать Сообщить модератору
 Re: Хранение отрезка времени в часовом формате  [new]
Andrey Sribnyak
Member

Откуда: Киев
Сообщений: 600
НЕ самое элегантное решение... но вроде решение:

declare @t table (dt datetime, tm char(8))

insert into @t values ('20120310', '20:29:10'), ('20120311','29:03:10'),('20120314','25:00:00')


select dateadd(ss,cast(parsename(replace(tm,':','.'),1) as int),dateadd(mi,cast(parsename(replace(tm,':','.'),2)as int),dateadd(hh,cast(parsename(replace(tm,':','.'),3)as int),dt)))
from @t
12 июн 12, 20:15    [12704260]     Ответить | Цитировать Сообщить модератору
 Re: Хранение отрезка времени в часовом формате  [new]
Владимир СА
Member

Откуда:
Сообщений: 7915
pkarklin
ПаWWWлОдАрЕц
"Хотелось бы не танцевать с бубнами."

А придется...

Че-то я особых танцев с бубнами и не вижу... Действительно в секундах хранить...

К сообщению приложен файл. Размер - 5Kb
13 июн 12, 07:20    [12705390]     Ответить | Цитировать Сообщить модератору
 Re: Хранение отрезка времени в часовом формате  [new]
ПаWWWлОдАрЕц
Member

Откуда: NSK-PVL
Сообщений: 135
Andrey Sribnyak
НЕ самое элегантное решение... но вроде решение:
declare @t table (dt datetime, tm char(8))
insert into @t 
values 
('20120310', '20:29:10'), 
('20120311','29:03:10'),
('20120314','25:00:00')
select dateadd(ss,cast(parsename(replace(tm,':','.'),1) as int),dateadd(mi,cast(parsename(replace(tm,':','.'),2)as int),dateadd(hh,cast(parsename(replace(tm,':','.'),3)as int),dt)))
from @t


Спасибо за ответ, НО, это не то, результат получается такой как я и предполагал, т.е. при наличии времени более 24:00:00 происходит увеличение даты и время вычитатеся.
Результат вашего кода ниже. Последние две строки это явно демонстрируют

dt tm COLUMN1
10.03.2012 20:29:10 10.03.2012 20:29:10
11.03.2012 29:03:10 12.03.2012 5:03:10
14.03.2012 25:00:00 15.03.2012 1:00:00

Вопрос остается открытым.
13 июн 12, 07:24    [12705393]     Ответить | Цитировать Сообщить модератору
 Re: Хранение отрезка времени в часовом формате  [new]
Владимир СА
Member

Откуда:
Сообщений: 7915
Ну и простецкий вариант...
declare @t table (dt int)
INSERT INTO @t (dt) VALUES (142092),(55692);
SELECT dt, CAST(dt/3600 as varchar) + ':' + CAST(dt%3600/60 as varchar) + ':' + CAST(dt%3600%60 as varchar) as c_dt FROM @t

Можно конечно и подретушировать, когда числа времени меньше 10 (часов/минут/секунд)...
13 июн 12, 07:43    [12705408]     Ответить | Цитировать Сообщить модератору
 Re: Хранение отрезка времени в часовом формате  [new]
Владимир СА
Member

Откуда:
Сообщений: 7915
После ретуши...
declare @t table (dt int)
INSERT INTO @t (dt) VALUES (142092),(55692),(18486),(60),(0);
SELECT dt, RIGHT('0' + CAST(dt/3600 as varchar),2) + ':' + RIGHT('0' + CAST(dt%3600/60 as varchar),2) + ':' + RIGHT('0' + CAST(dt%3600%60 as varchar),2) as c_dt FROM @t


К сообщению приложен файл. Размер - 4Kb
13 июн 12, 08:00    [12705424]     Ответить | Цитировать Сообщить модератору
 Re: Хранение отрезка времени в часовом формате  [new]
ПаWWWлОдАрЕц
Member

Откуда: NSK-PVL
Сообщений: 135
Всем спасибо. Скорре всего придется запихивать данные как varchar, наваять вьюху с вычисляемым полем для перевода varchar'овских данных в секунды, а для обработки использовать этот вариант.

+

Владимир СА
После ретуши...
declare @t table (dt int)
INSERT INTO @t (dt) VALUES (142092),(55692),(18486),(60),(0);
SELECT dt, RIGHT('0' + CAST(dt/3600 as varchar),2) + ':' + RIGHT('0' + CAST(dt%3600/60 as varchar),2) + ':' + RIGHT('0' + CAST(dt%3600%60 as varchar),2) as c_dt FROM @t



Если больше нет вариантов, то вопрос можно считать закрытым.
13 июн 12, 08:50    [12705560]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить