Standard Deviation for Times

Question

Is there a T-SQL coding best practice to get an accurate standard deviation value for a group of times? STDEV doesn't like the time data type. I'm thinking that maybe a conversion to minutes as an integer, but a conversion from/to what? Any suggestions?

Answer 1

You can convert a time value to a corresponding number of desired time units by using the DATEDIFF() and DATEDIFF_BIG() functions:

DATEDIFF(MINUTE, CAST('00:00' AS time), YourTimeColumn) AS Minutes
DATEDIFF(SECOND, CAST('00:00' AS time), YourTimeColumn) AS Seconds
DATEDIFF(MILLISECOND, CAST('00:00' AS time), YourTimeColumn) AS Milliseconds

Basically, you are just obtaining the difference, in the given units, between the timestamp of 00:00:00.0000000 and your time value.

You can put the entire expression inside STDEV:

STDEV(DATEDIFF(MINUTE, CAST('00:00' AS time), YourTimeColumn))

That, of course, will give you the deviation in minutes. If you need to convert it back to time, you can do it by applying DATEADD() to the STDEV result:

DATEADD(
  MINUTE
, CAST('00:00' AS time)
, STDEV(
    DATEDIFF(
      MINUTE
    , CAST('00:00' AS time)
    , YourTimeColumn
    )
  )
)

The final expression becomes quite cumbersome this way. You can try simplifying it by defining some of the intermediate expressions as columns. One way is to use CROSS APPLY. For instance, here is how you can define and use a temporary computed column for the DATEDIFF result:

SELECT
  ...
  DATEADD(MINUTE, CAST('00:00' AS time), STDEV(tmp.TimeMinutes)) AS TimeStDev,
  ...
FROM
  dbo.YourTable AS data
  CROSS APPLY
  (
    SELECT
      DATEDIFF(MINUTE, CAST('00:00' AS time), data.YourTimeColumn)
  ) AS tmp (TimeMinutes)
  ...
WHERE
  ...
;