CODEWITHSUNDEEP
mysql
user2514863
user2514863

Reputation: 35

How to get total hour?

Name|| day||ActivityDate ||TimeIn  || TimeOut ||
Ade || 20 || 2013-08-20 || 10:06:09 || 18:21:03 ||
Ade || 21 ||2013-08-27  || 11:00:34 || 18:06:56 ||
Ade || 22 || 2013-08-28 || 09:56:29 || 17:59:56 ||

This is my query :

select 
tot=sum(DATEDIFF(hh ,TimeIn ,TimeOut )) as TotalHourAndMinute
from report

And error :

1582 - Incorrect parameter count in the call to native function 'DATEDIFF'

This is my table in datable.. I don't know how to get total hour like TimeOut-TimeIn..

fyi, i have a lot of data in this table.. not only this 3..

i hope you guys clear...

Name|| day||ActivityDate ||TimeIn  || TimeOut   || TotalHourAndMinute
Ade || 20 || 2013-08-20 || 10:00:00 || 18:30:00 ||   8.5
Ade || 21 ||2013-08-27  || 11:00:34 || 18:06:56 ||   7.something
Ade || 22 || 2013-08-28 || 09:56:29 || 17:59:56 ||   7.something

i want it will be like this..

UPDATE

Upvotes: 2

Views: 158

Answers (3)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

Well, if you're using MySQL the correct format for the function is this:

DATEDIFF(expr1,expr2)

More information about DATEDIFF, here. Also, all available time functions for MySQL are here.

But, if you want to see the difference in hours between two dates, use TIME_DIFF.

TIMEDIFF(expr1,expr2)

And documentation about TIME_DIFF here.

But strictly to your case, you should write your query like this:

SELECT tot = sum(HOUR(TIMEDIFF(TimeIn, TimeOut))) AS TotalHourAndMinute
FROM report

UPDATE:

Now, after updating your question I understand what you want.

The query you need to use is this:

SELECT 
      NAME, 
      DAY, 
      ActivityDate, 
      SUM(TIMEDIFF(TimeOut, TimeIn)) as TotalHourAndMinute
FROM REPORT
WHERE (TimeOut IS NOT NULL) AND (TimeIn IS NOT NULL)
GROUP BY NAME, DAY, ActivityDate

Upvotes: 1

Jan Fabry
Jan Fabry

Reputation: 7351

If you aggregate time values you will get a result in seconds, to convert it back to time you call SEC_TO_TIME. This gives you:

SELECT
    SEC_TO_TIME(SUM(TIMEDIFF(TimeOut, TimeIn))) AS TotalHourAndMinute
FROM
    report

Upvotes: 0

Dhwani
Dhwani

Reputation: 7628

use:

SELECT HOUR(TIMEDIFF(TimeOut,TimeIn)) AS hour from report;

For more information visit this site.

Upvotes: 0

Related Questions