CODEWITHSUNDEEP
sqlsql-servercastinggroup-byaverage
Luis Cabanillas
Luis Cabanillas

Reputation: 33

SQL Average Time from timestamp stored as string

I have two columns called date and starthour in a Microsoft SQL Server table.

Both columns are char; Another guy make it and I don't know why it was built in this way. :)

date                     starthour

20/01/2011               8:10:00
20/01/2011               8:20:00
20/01/2011               8:30:00
20/01/2011               8:40:00
21/01/2011               8:10:00
21/01/2011               8:20:00
21/01/2011               8:30:00

I want to determine the average starthour for each date.

date                     starthour
20/01/2011               8:25:00
21/01/2011               8:20:00

I tried the following:

SELECT date, Avg(cast(starhour as datetime())) AS starhour
FROM table
GROUP BY date

but it doesn't work.

Upvotes: 3

Views: 8055

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

SELECT [date], 
  CAST(DATEADD(second, AVG(DATEDIFF(second, 0 , starhour)), '00:00:00') AS time)
FROM dbo.test17
GROUP BY [date]

Demo on SQLFiddle

Upvotes: 2

Kyle Hale
Kyle Hale

Reputation: 8120

Let's not beat around the bush: you should fix this schema as soon as possible.

In the meantime, this will return the correct value. 

select [date], 
 CONVERT(VARCHAR(8), 
 cast((avg(cast(cast(starhour as datetime)  as float)))   as datetime) , 108)
  from table
group by [date]

Upvotes: 1

Patashu
Patashu

Reputation: 21773

Do not store dates, datetimes or timestamps as varchar. For instance, MySQL has native types for all three of these types, which guarantees that you won't have invalid data in them, and that MySQL will know how to treat them: http://dev.mysql.com/doc/refman/5.1/en/datetime.html

What you should do is have a single field, date, that is of timestamp type, containing both the date and the time. You should refactor your database and transfer over all your data to the new format by casting it, so that you never have to worry about again.

After that what you need to do differs based on your SQL flavour. For example, this answer will be for MySQL, but every flavour has different datetime functions (unfortunately!).

MySQL's datetime functions are http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Untested but something like:

GROUP BY EXTRACT(day from date)

AVG(EXTRACT(hour from date)*60 + EXTRACT(minute from date))

Upvotes: 1

Related Questions