Get total hours worked in a day mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ullasvk
Forum Newbie
Posts: 21
Joined: Fri Feb 13, 2009 11:55 pm

Get total hours worked in a day mysql

Post by ullasvk »

Hi all,

I have a mysql table where employee login logout timings are noted.Here in the in-out coloumn 1-represents login and 0-represents logout.

Code: Select all

  [id]   [User_id]           [Date_time]                 [in_out]
    1       1          2011-01-20 09:30:03                  1
    2       1          2011-01-20 11:30:43                  0
    3       1          2011-01-20 11:45:12                  1
    4       1          2011-01-20 12:59:56                  0
    5       1          2011-01-20 13:33:11                  1
    6       1          2011-01-20 15:38:16                  0
    7       1          2011-01-20 15:46:23                  1
    8       1          2011-01-20 17:42:45                  0
Is it possible to retreieve total hours worked in a day by a user using single query?? I tried a alot but all in vain.I can do this in php using array but unable to do so using single query.

Thanks in advance...
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Get total hours worked in a day mysql

Post by VladSun »

Two questions to ask first:
- is it possible that an user may log in before midnight and logout after midnight?
- is it possible that an user may be logged in incorrectly - i.e. login/logout events are not recorded into the DB?

My solution would be:

Code: Select all

set @start_work_period:=0, @work_sum:=0;
select 
	in_out,
	if (in_out=1, @start_work_period := UNIX_TIMESTAMP(date_time), 0) as _xx,
	if (in_out=0, @work_sum := UNIX_TIMESTAMP(date_time) - @start_work_period, 0) as current_work_time
from 
	worktime
where
	date_time between '2011-01-20 0:0:0' and '2011-01-20 23:59:59'
	and 
	user_id = 1
group by 
	date_time
having
	in_out=0
That will give you the user's login-logout periods for a date.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply