mysql - How to write a script for count working time employee? -

mysql - How to write a script for count working time employee? -

help me, please!

at table - date, time, person, source. updated new values ​​when employee passing through checkpoint, can leave / came several times per day.

+---------------+----------+--------+-------------+ | date | time |person |source | +---------------+----------+--------+-------------+ | 01.08.2014 | 08:42:08 | name1 | enter1 | +---------------+----------+--------+-------------+ | 01.08.2014 | 09:42:12 | name1 | exit1 | +---------------+----------+--------+-------------+ | 01.08.2014 | 10:22:45 | name1 | enter2 | +---------------+----------+--------+-------------+ | 01.08.2014 | 18:09:11 | name1 | exit2 | +---------------+----------+--------+-------------+

i need count each employee actual time spent @ work each day. table not editable. formed csv file. script runs once.

i think need this:

timestampdiff(minute, entertime, exittime)

for each employee 1 day. have poor knowledge in sql.

the date/time formats should stored in datetime/timestamp column. possible convert them, although ugly (there's improve way...):

> select concat(str_to_date('01.08.2014', '%m.%d.%y'), ' ', '08:42:08'); 2014-01-08 08:42:08

now suppose times unix timestamps. employ arrives @ t0 , leaves @ t1. time @ work (t1-t0) seconds. suppose he arrives @ t0, leaves break @ t1, returns @ t2, , leaves day @ t3. total time @ work (t1-t0) + (t3-t2) = (t1+t3)-(t0+t2). in general: time @ work given day sum of arrival times subtracted sum of departure times.

using times:

1389188528 enter1 1389192132 exit1 1389194565 enter2 1389222551 exit2

we see total time @ work is: 1389222551 + 1389192132 - (1389188528 + 1389194565) = 31590, or 8 hours , 47 minutes. remains converting unix timestamps (unix_timestamp()) , applying reasoning via sql. in next example, have added info table named work_log , assumed when source begins exit or enter, respectively referring departure or arrival.

select person, date(dt) day, sum(if(`source` 'enter%', -1, 1)*unix_timestamp(dt))/3600 hours (select concat(str_to_date(`date`, '%m.%d.%y'), ' ', `time`) `dt`, `person`,`source` work_log) wl grouping person,day; +--------+------------+--------------+ | person | day | hours | +--------+------------+--------------+ | name1 | 2014-01-08 | 8.7750000000 | +--------+------------+--------------+ 1 row in set (0.00 sec)

there probably cleaner ways of doing that.

mysql