7

I'm struggling with what I thought would be a simple SQL query. Running SQL Server 2014

I have an SQL table, "Visits":

Id | EntryTime | Duration

And I want to find the average entry TIME OF DAY between two dates, taking into account all records between those dates.

so if my EntryTime field between my dates is:

2016-04-28 12:00:00
2016-04-20 10:00:00
2016-04-19 08:00:00
2016-04-17 10:00:00

Then the average time returned should just be:

10:00:00

The date should not be taken into account at all, and it should be returned in string format, or a manner which returns ONLY 10:00:00.

JonnyKnottsvill
  • 1,123
  • 2
  • 16
  • 39

3 Answers3

16
create table mytimes(
   id int identity,
   mydatetime datetime
)

insert into mytimes (mydatetime) values ('2016-04-28 12:00:00')
insert into mytimes (mydatetime) values ('2016-04-20 10:00:00')
insert into mytimes (mydatetime) values ('2016-04-19 08:00:00')
insert into mytimes (mydatetime) values ('2016-04-17 10:00:00')

SELECT Cast(DateAdd(ms, AVG(CAST(DateDiff( ms, '00:00:00', cast(mydatetime as time)) AS BIGINT)), '00:00:00' ) as Time ) 
from mytimes
-- where mydatetime between XXX and YYY

SELECT convert(varchar(8), Cast(DateAdd(ms, AVG(CAST(DateDiff( ms, '00:00:00', cast(mydatetime as time)) AS BIGINT)), '00:00:00' ) as Time )) 
from mytimes
-- where mydatetime between XXX and YYY

output-1 10:00:00.0000000 - this is an actual Time type that you can do more with if needed

output-2 10:00:00 - this is output as a varchar(8)

Add your where clause as you see fit

The steps include

  • Casting to a Time type from a DateTime.
  • Using the AVG on Time, this is not supported by type Time so you have to first convert Time to milliseconds.
  • Converting the milliseconds back to a Time type
  • To avoid Arithmetic overflow error converting expression to data type int you can cast the result of DateAdd to a BigInt. Alternatively you can use seconds instead of milliseconds in the DateDiff function which should work unless your result set is overly large.

SO Sources:

Community
  • 1
  • 1
Igor
  • 60,821
  • 10
  • 100
  • 175
  • 1
    @GibralterTop - The OP did specify the result but never if it should be varchar or type Time. Fair enough though, I added another line with a convert to varchar(8) to show that either/or could be retrieved. – Igor Apr 28 '16 at 15:08
  • I get the following error on this "Arithmetic overflow error converting expression to data type int." – JonnyKnottsvill Apr 28 '16 at 15:15
  • @JonnyKnottsvill - what is the type of your `EntryTime` column? In the complete sample code I provided I do not get any errors. That code uses the same values you provided above but uses the column type DateTime. Maybe your result set is much larger? – Igor Apr 28 '16 at 15:17
  • My result set is moderately large. It is a datetime column. These queries are running in a third party data dashboard tool but the error seems to be happening at the destinations SQL Server level – JonnyKnottsvill Apr 28 '16 at 15:22
  • @JonnyKnottsvill - ok, i will try with a larger data set. I did just change it to cast the result of DateAdd to a BIGINT which might fix the problem. Can you give it another go? – Igor Apr 28 '16 at 15:23
  • @JonnyKnottsvill - just tested and reproduced the issue with a table of my own but the fix I just posted works. If you try again you should have no problems. – Igor Apr 28 '16 at 15:25
  • 1
    Works perfectly. Thanks for both type outputs as well and the explanations so I can follow! – JonnyKnottsvill Apr 28 '16 at 15:27
  • Rather than doing the `convert(varchar(8)` I would CAST as time(0). This is because the convert doesn't round the milliseconds correctly whereas the CAST AS TIME(0) does. So this doesn't round correctly: `SELECT convert(varchar(8), Cast(DateAdd(ms, AVG(CAST(DateDiff( ms, '00:00:00', cast(mydatetime as time)) AS BIGINT)), '00:00:00' ) as Time )) from mytimes` whereas this rounds correctly `SELECT Cast(DateAdd(ms, AVG(CAST(DateDiff( ms, '00:00:00', cast(mydatetime as time)) AS BIGINT)), '00:00:00' ) as Time(0) ) from mytimes` does – Shaheen K Jun 18 '18 at 10:37
3
SELECT CONVERT(TIME, DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, CONVERT(TIME, EntryTime ))), 0)) 
FROM Visits
WHERE EntryTime >= @begin AND EntryTime <= @end

The idea came from here: T-SQL calculating average time

Community
  • 1
  • 1
Ilya Chumakov
  • 23,161
  • 9
  • 86
  • 114
0

Yap, you can use the Time() to get this done.

Your query becomes like this (modify accordingly)

SELECT COUNT(*) FROM Visits WHERE TIME(EntryTime) > '06:00' AND EntryTime < '18:00';
Nabeel Khan
  • 3,715
  • 2
  • 24
  • 37
  • 1
    The OP is asking for the average time, not a count of the records. Also I do not believe that `TIME(DateTime)` is a built in function in Sql Server, if so can you reference the documentation? – Igor Apr 28 '16 at 15:11