March 26, 2016 at 6:13 pm
Hello All,
I have source table with column nvarchar(20). the data looks like below.
Duration
120:06:31.150
120:06:31.165
238:21:19.875
266:45:41.472
287:03:25.184
I need to dump it into destination and i do not want to use nvarchar(20) as datatype as I think there should a designated dataype to measure duration in HHH:MM:SS:NN
can you tell me which datatype to use? TIME is not working as Hours section has 3 digits
March 27, 2016 at 12:15 am
Mac1986 (3/26/2016)
Hello All,I have source table with column nvarchar(20). the data looks like below.
Duration
120:06:31.150
120:06:31.165
238:21:19.875
266:45:41.472
287:03:25.184
I need to dump it into destination and i do not want to use nvarchar(20) as datatype as I think there should a designated dataype to measure duration in HHH:MM:SS:NN
can you tell me which datatype to use? TIME is not working as Hours section has 3 digits
In order to avoid doing all kind of arithmetic operations on the data, it is probably best to use the smallest unit which in this case are milliseconds. It would have to be BIGINT as 999:59:59.999 would exceed 2^31-1 by 1452516352. The maximum duration in milliseconds one can store in BIGINT is 292271023 years, should be enough for most purposes.
😎
Quick example adjusted to the HHH:MM:SS:NNN format
/********************************************************************
Storing duration in milliseconds as BIGINT
Min: 0
Max: 9223372036854775807
-> Hours: 2562047788015.21
-> Days: 106751991167.30
-> Years: 292271023.04
********************************************************************/
DECLARE @TBL_DURATION TABLE
(
D_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,D_STR VARCHAR(30) NOT NULL
);
INSERT INTO @TBL_DURATION(D_STR)
VALUES ( '000:00:00.000')
,( '000:00:00.001')
,( '000:00:00.010')
,( '000:00:00.100')
,( '000:00:01.000')
,( '000:01:00.000')
,( '001:00:00.000')
,( '001:59:59.999')
,( '010:00:00.000')
,( '100:00:00.000')
,( '999:59:59.999')
,( '9999:59:59.999')
,( '99999:59:59.999')
,( '999999:59:59.999')
,( '9999999:59:59.999')
,( '99999999:59:59.999')
,( '999999999:59:59.999')
,( '9999999999:59:59.999')
,( '99999999999:59:59.999')
,( '999999999999:59:59.999')
,('2562047788015:12:55.807')
;
SELECT
TD.D_ID
,(CHARINDEX(':',TD.D_STR,1) -1) AS HOUR_DIGITS
,(CONVERT(BIGINT,SUBSTRING(TD.D_STR,1,CHARINDEX(CHAR(58),TD.D_STR,1) - 1),0) * 3600000)
+ DATEDIFF(MILLISECOND,CONVERT(TIME,'00:00:00.0000000',0),CONVERT(TIME(7),'00:'
+ SUBSTRING(TD.D_STR,CHARINDEX(CHAR(58),TD.D_STR,1) + 1,20),0)
) AS DURATION_MILLISECONDS
,TD.D_STR
FROM @TBL_DURATION TD;
Output
D_ID HOUR_DIGITS DURATION_MILLISECONDS D_STR
----------- ----------- --------------------- ------------------------
1 3 0 000:00:00.000
2 3 1 000:00:00.001
3 3 10 000:00:00.010
4 3 100 000:00:00.100
5 3 1000 000:00:01.000
6 3 60000 000:01:00.000
7 3 3600000 001:00:00.000
8 3 7199999 001:59:59.999
9 3 36000000 010:00:00.000
10 3 360000000 100:00:00.000
11 3 3599999999 999:59:59.999
12 4 35999999999 9999:59:59.999
13 5 359999999999 99999:59:59.999
14 6 3599999999999 999999:59:59.999
15 7 35999999999999 9999999:59:59.999
16 8 359999999999999 99999999:59:59.999
17 9 3599999999999999 999999999:59:59.999
18 10 35999999999999999 9999999999:59:59.999
19 11 359999999999999999 99999999999:59:59.999
20 12 3599999999999999999 999999999999:59:59.999
21 13 9223372036854775807 2562047788015:12:55.807
March 27, 2016 at 6:00 pm
Mac1986 (3/26/2016)
Hello All,I have source table with column nvarchar(20). the data looks like below.
Duration
120:06:31.150
120:06:31.165
238:21:19.875
266:45:41.472
287:03:25.184
I need to dump it into destination and i do not want to use nvarchar(20) as datatype as I think there should a designated dataype to measure duration in HHH:MM:SS:NN
can you tell me which datatype to use? TIME is not working as Hours section has 3 digits
The question is, what do you intend to do with the duration? Will it be SUMed, for example. If it is, in what format would you want to display the SUM? For that matter, in what format would you want to display the individual durations? And, do you really need the durations to have millisecond resolution or are seconds good enough?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2016 at 7:56 am
I'm guessing that Jeff is thinking on taking a similar path to this, but wants to be sure about the possible restrictions.
You'll notice that I'm using datetime2(3) instead of datetime. That's to avoid "rounding" and keep milliseconds exact. It's easier to recover the original format and you're saving one byte per row. But you're also losing flexibility of working with simple numbers.
So many things to take into consideration.
CREATE TABLE #Test(
Duration varchar(20),
DTDuration datetime2(3)
);
INSERT INTO #Test(Duration)
VALUES
('120:06:31.150'),
('120:06:31.165'),
('238:21:19.875'),
('266:45:41.472'),
('287:03:25.184');
UPDATE #Test
SET DTDuration = DATEADD( HH, CAST( LEFT( Duration, CHARINDEX(':', Duration) - 1) AS int)
, CAST( STUFF(Duration, 1, CHARINDEX(':', Duration), '1900-01-01T00:') AS datetime2(3)));
SELECT *,
STUFF(CONVERT(VARCHAR(20),DTDuration,114),1,2,DATEDIFF(hh,0,DTDuration))
FROM #Test;
GO
DROP TABLE #Test;
March 28, 2016 at 8:31 am
Luis Cazares (3/28/2016)
I'm guessing that Jeff is thinking on taking a similar path to this, but wants to be sure about the possible restrictions.
Exactly. And, there's some pretty nifty stuff you can do with a persisted computed column if you remember that dates and times can be easily converted to decimal days and back again.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2016 at 8:42 am
Jeff Moden (3/28/2016)
Luis Cazares (3/28/2016)
I'm guessing that Jeff is thinking on taking a similar path to this, but wants to be sure about the possible restrictions.Exactly. And, there's some pretty nifty stuff you can do with a persisted computed column if you remember that dates and times can be easily converted to decimal days and back again.
Easily converted only when not using the new date data types. 🙁
March 28, 2016 at 8:52 am
Luis Cazares (3/28/2016)
Jeff Moden (3/28/2016)
Luis Cazares (3/28/2016)
I'm guessing that Jeff is thinking on taking a similar path to this, but wants to be sure about the possible restrictions.Exactly. And, there's some pretty nifty stuff you can do with a persisted computed column if you remember that dates and times can be easily converted to decimal days and back again.
Easily converted only when not using the new date data types. 🙁
Heh... once again, exactly! MS sure put the kibosh on a useful feature. But, even then, it's not a killer. I just need to know what resolution is actually necessary so that we know whether it's going to be drop dead simple or a little more complicated. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2016 at 10:26 am
Thanks for the response Eirikur Eiriksson. I'll use your idea here. I think i'll just do a BIGINT as datatype and covert the duration to seconds while i'm pumping it into the table.
we can archive this thread now.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply