Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can anyone please help me on this..

I have a column in my table that stores the duration in an nvarchar field formatted as '00:00:00'.

How do I calculate the average duration? I read some other blogs and also used cast techniques like the one below but couldn't solve my problem...

cast(cast(avg(cast(CAST(Duration as datetime) as float)) as datetime) as time) AvgTime

Please help...

share|improve this question
7  
why oh why would you have that as a VARCHAR. Therein lieth your problem, my good man. –  Max Vernon Jun 17 at 20:35
    
That is already an existing table created by someone in our org... Any workarounds please, I'm open to change the data type also. in table.. –  Sai Jun 17 at 20:38
1  
def. of "lieth": archaic third-person singular simple present indicative form of lie. –  Max Vernon Jun 17 at 20:40
    
I would certainly consider changing the field type. But only after extensive testing. –  Max Vernon Jun 17 at 20:41
    
First thing, go and convince the "someone in our org" not to ever store date data in character-based fields. Show him the docs for the DATETIME data types. –  Max Vernon Jun 17 at 20:46

3 Answers 3

Store duration in seconds as an integer; then average is quite easy. Right now you're trying to take an average of a string converted to a float converted to a datetime converted to a time. If that doesn't sound wrong to you, read it again. Then consider that time represents a point time. What is the average of 3:12 AM and 4:57 PM? Meet in the middle?

Don't be tempted to change this from nvarchar to time. Again, time is a point in time, not an interval. You should be storing start and end values as datetime; you can always calculate duration from that (typically I see duration as an additional nonsense column that doesn't need to be stored in the first place).

The formatting as hh:mm:ss should not happen at storage time, only at presentation time. And no idea why you would ever use nvarchar for this in the first place - what Unicode characters do you expect to support in hh:mm:ss?

In the meantime:

SELECT CONVERT(TIME(0), DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, CONVERT(DATETIME, 
  CASE WHEN ISDATE(Duration) = 1 THEN Duration END))),0)) -- in case of garbage data 
FROM dbo.your_table_name;

See how messy that is? Wouldn't it be much easier as:

SELECT AVG(Duration) FROM dbo.your_table_name;

Or:

SELECT AVG(DATEDIFF(SECOND, [start], [end])) FROM dbo.your_table_name;

And then format as hh:mm:ss in the client language? Yes, I think so.

share|improve this answer
    
+1 for reason and thought. Although if all I need is a duration, sometimes I might store that as datetime; if I'm feeling particularly feisty. Sometimes duration goes for a long, long time, and datetime might be an efficient enough way of doing it while ensuring nothing other than valid durations are stored. Thoughts? –  Max Vernon Jun 17 at 20:54
    
@Max I would never store duration as datetime - what is the date component? What if something goes longer than 24 hours? Now you have Jan 2nd 1900? Same goes for time - what if duration is longer than 24 hours? What is wrong with either (a) storing start and end, and calculating duration in whatever unit you want, or (b) storing the duration you want as an integer? What do you get out of storing what essentially amounts to an integer as a datetime? –  Aaron Bertrand Jun 17 at 20:56
    
Thanks Aaron.. I understand.. thanks for your suggestions.. –  Sai Jun 17 at 21:01
    
@AaronBertrand - I guess I tend to think of datetime as a decimal(10,5); that is a whole number representing the number of days, and a decimal number representing a fraction of a day. I guess at the end of the day it's really just a semantic difference, and it probably should be stored as an INT representing the number of seconds if you don't care about sub-second resolution. –  Max Vernon Jun 17 at 21:14
    
@max what a datetime type represents and how it is stored internally are two different things - I think you're comparing semantic differences in the wrong place. A datetime is a point in time. It doesn't really make sense to use it to store a duration (again regardless of internal implementation). What if you want to store duration in microseconds? Or hours? Or weeks? Now you have to come up with a different solution... –  Aaron Bertrand Jun 17 at 21:21

I assume you must have invalid data stored in your nvarchar field. I created a simple test to obtain average duration, which works:

DECLARE @T TABLE
(
    duration NVARCHAR(8)
);
INSERT INTO @T VALUES ('00:00:05');
INSERT INTO @T VALUES ('00:01:04');
INSERT INTO @T VALUES ('00:02:03');
INSERT INTO @T VALUES ('03:00:02');
INSERT INTO @T VALUES ('04:00:01');

SELECT CAST(AVG(CAST(CAST(duration AS DATETIME) AS DECIMAL(10,5))) AS DATETIME)
FROM @T;

This returns:

enter image description here

If you want the result in time format, you could do the following, although I would recommend not doing this part in T-SQL - you should do presentation layer stuff in the presentation layer.

SELECT CONVERT(VARCHAR(255), CAST(AVG(CAST(CAST(duration AS DATETIME) AS DECIMAL(10,5))) AS DATETIME), 108)
FROM @T;

enter image description here

I've been convinced by this question, and Aaron's excellent answer, that duration should be stored either using start and end fields using some type of date data-type, or as a simple integer expressing the duration in the smallest necessary increment. For instance, if you are only interested in how many weeks something took, simply store the number of weeks as an INT. To see the chat transcript that convinced me, see http://chat.stackexchange.com/transcript/message/16134865#16134865

share|improve this answer
2  
+1 but of course the cast to datetime won't work if any garbage data has entered the column. –  Aaron Bertrand Jun 18 at 1:38

If you can't afford to refactor this field as an integer right now but you need better performance or simplicity for aggregate calculations, you may want to consider a computed field:

ALTER TABLE timetable ADD DurationSeconds AS DATEDIFF(SECOND, '00:00:00', Duration);

You can then index this field if needed.

Based on a comment below, here's an alternative that seems to avoid conversion of the 00:00:00 argument (which I would've thought would be part of the compilation of the statement but I guess not!):

ALTER TABLE timetable ADD DurationSeconds AS DATEDIFF(SECOND, 0, Duration);

And here's a version that avoids DATEDIFF() entirely:

CAST(CAST('00:30:00' AS datetime) AS float) * 86400

My original answer used SUBSTRING() to directly convert the hours, minutes, and seconds to a total number of seconds. My testing found that DATEDIFF() was slower, but YMMV. Here's the alternate method:

ALTER TABLE timetable ADD DurationSeconds AS
    3600 * CAST(LEFT(Duration, 2) AS int)
    + 60 * CAST(SUBSTRING(Duration, 4, 2) AS int)
    + CAST(RIGHT(Duration, 2) AS int);

Regardless which method you choose, you get a dynamically-calculated value that you can pass to AVERAGE(), SUM(), etc. as needed.

share|improve this answer
1  
You could just get the difference in seconds between '00:00:00' and Duration instead of all the string manipulations. –  Andriy M Jun 18 at 9:45
    
Andriy, after testing, I agree, DATEDIFF() is much faster at converting the nvarchar than intrinsic string functions and integer casts. Answer updated. –  richardtallent Jun 18 at 16:10
1  
Strangely, thanks to you I've just discovered that DATEDIFF(SECOND, '00:00:00', Duration) is actually slower than your original suggestion, but replacing the '00:00:00' (a string) with a 0 (an integer) did make it faster (than your method). Don't know why we've got different conclusions. (If this matters, I didn't use a table in my tests, because I only wanted to test the expressions, so I assigned same expression to a variable in a loop and measured the time.) –  Andriy M Jun 18 at 16:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.