May 24, 2013 at 11:59 am
CREATE TABLE #AmountOfTime
(
DURATIONTIME
)
INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:22:04.1600000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:12:21.5800000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('00:41:52.5170000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('05:24:58.4530000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('09:19:34.9500000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:59:44.4000000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('03:00:53.3170000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('06:03:16.6430000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('05:50:43.9300000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('10:10:51.3600000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:26:35.8430000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('08:55:28.1970000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('03:24:47.2830000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('02:11:15.0770000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:56:58.9070000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:28:34.0070000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('06:21:41.2800000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('02:00:09.6170000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('06:45:57.8800000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('09:48:41.8670000')
Hi,
The times you see are, it took this many hours, min, sec, ms, to go from point A to point B; thus each record is how long it took for each uniqe trip...
I've googled and search here, and cant seem to find or understand how to find out what the average hours, min, sec, ms for these 20 records???
Thank you,
John
May 24, 2013 at 12:38 pm
This probably isn't what you want to hear, but I believe the time data type is meant to store a point in time, not a duration of time. So if you're storing durations, I think you'd be better off storing the values as numbers, taking the average and then converting the number to hours, minutes and seconds.
May 24, 2013 at 12:40 pm
Hi,
Any reply is a good reply... lol
What data type, I keep getting errors on converting to int... not sure which double precision to use to be able to convert back to hours, min, secs... float I'm not sure about either...
Thanks,
john
May 24, 2013 at 12:53 pm
If you convert everything into seconds and then save the decimal portion of the number for the fractions of a second, you'd have a maximum value of 86,400 seconds in a day. I'd first figure out if you need to support multiple days and then assign the data type accordingly. I wouldn't think that you'd need anything more precise than 1/1000 of a second, but it depends on your specific business requirements.
This may help: http://technet.microsoft.com/en-us/magazine/dd424925.aspx
I would lean towards the numeric data type myself. Float is great, but MS classifies them as approximate numeric types and from your data, it looks like you want something that can be compared to a fixed number of decimals.
May 24, 2013 at 12:53 pm
The code is an exercise in date/time functions and data type conversions. Here are the enumerated steps:
1. Use DATEDIFF to calculate the difference, in milliseconds, from midnight to the time specified.
2. Use AVG to take the average number of milliseconds.
3. Use DATEADD to add that number of milliseconds to midnight of any given date.
4. Use CONVERT to extract and display only the time portion of the date calculated in the prior step.
select convert(varchar(12), dateadd(MILLISECOND, AVG(datediff(MILLISECOND, '00:00:00.0', Duration)), '1/1/1900'), 114) from #AmountOfTime
There would be issues if the average duration ever exceeded 24 hours. Since your times do not appear to be flirting with that max limit for this approach, perhaps this will be sufficient.
May 24, 2013 at 12:56 pm
jsteinbeck-618119 (5/24/2013)
Hi,Any reply is a good reply... lol
What data type, I keep getting errors on converting to int... not sure which double precision to use to be able to convert back to hours, min, secs... float I'm not sure about either...
Thanks,
john
As Ed already mentioned you are using the incorrect datatype for this. If your duration ever exceeds 24 hours your are toast.
You could however use some date math here. I don't exactly condone this approach but I think the math should be correct.
with MyAverage as
(
select AVG(DATEDIFF(second, cast('' as datetime), CAST(duration as datetime))) as AvgDuration
from #AmountOfTime
)
select AvgDuration / 3600 as AvgHours, (AvgDuration % 3600) / 60 as AvgMinutes, ((AvgDuration % 3600) % 60) as Seconds
from MyAverage
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2013 at 12:57 pm
Geoff posted while I was writing. I like his approach better than mine, unless you need them separated. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2013 at 1:15 pm
Thank you guys...
Geoff,
I'm getting the error "Arithmetic overflow error converting expression to data type int."
Is it because the column is a Data Type of Time?
May 24, 2013 at 1:18 pm
jsteinbeck-618119 (5/24/2013)
Thank you guys...Geoff,
I'm getting the error "Arithmetic overflow error converting expression to data type int."
Is it because the column is a Data Type of Time?
Do you get the same error using the code I posted?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2013 at 1:26 pm
Yes.... I assume you used my code as well? Difference is I have 800 records and you have 20... this is my max value 23:59:47.8000000
Thanks
May 24, 2013 at 1:34 pm
jsteinbeck-618119 (5/24/2013)
Yes.... I assume you used my code as well? Difference is I have 800 records and you have 20... this is my max value 23:59:47.8000000Thanks
I added that value. I also added NULL and empty string. It works just fine (both my code and Goeff's). I don't see how you would get a value too big or too small for that to work unless it is a different datatype.
Here are the extra inserts I used.
INSERT INTO #AmountOfTime (DURATION ) VALUES ('')
INSERT INTO #AmountOfTime (DURATION ) VALUES (NULL)
INSERT INTO #AmountOfTime (DURATION ) VALUES ('00:00:01.9380000')
INSERT INTO #AmountOfTime (DURATION ) VALUES ('23:59:47.8000000')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2013 at 2:00 pm
Your max time is now very close to a whole day. I would at least consider converting this to a numeric data type. As soon as you cross that 24-hour boundary, you're going to start having problems.
May 24, 2013 at 7:34 pm
The "time" data type has an upper limit one microsecond less than 24 hours. I don't know how you are capturing the duration value, but should it ever exceed 23.59:59.9999999 (the upper limit of the data type), the "time" data type will report an incorrect value. As Ed has pointed out at least twice, capturing this value as a numeric value of "milliseconds elapsed" (if that is sufficient precision) makes much more sense.
Having said that, if you still need to troubleshoot the conversion issue, you can at least pinpoint which part of the formula is triggering the data type conversion error by breaking the formula into parts and trying each one. That will at least tell you at what point the failure is happening and might give a clue as to what might be causing it.
Here is some code to help with that. Just successively remove the -- comment code out for each line one at a time. Only run one line from the SELECT section at a time, so add the -- comment code in front of each line that you are not testing that moment.
Incidentally, I realized that 0 is the datetime equivalent of midnight on 1/1/1900, so it can substitute for the longer strings I was using before. I doubt that change would solve the issue, but it does shorten the code somewhat.
select
Step1 = datediff(MILLISECOND, 0, Duration)
-- Step2 = AVG(datediff(MILLISECOND, 0, Duration))
-- Step3 = dateadd(MILLISECOND, AVG(datediff(MILLISECOND, 0, Duration)), 0)
-- Step4 = convert(varchar(12), dateadd(MILLISECOND, AVG(datediff(MILLISECOND, 0, Duration)), 0), 114)
from #AmountOfTime
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply