-3

Below table shows the checkin & checkout time of employee 

Emp_id report_date report_time 

 11     2014-12-01 08:02:31
 21     2014-12-01 08:13:04
 11     2014-12-01 18:03:41
 21     2014-12-01 16:36:02
Shivaraj patil
  • 17
  • 8

1 Answers1

0

You need to convert time values to seconds check this post:

MySQL: how to get the difference between two timestamps in seconds

I'll share with you a working code but it is important that you understand what is going on with it.

SELECT `Emp_id`, `report_date`, min(`report_time`) AS chekin, 

max(`report_time`) AS chekout,

( (TIME_TO_SEC(TIMEDIFF(max(`report_time`), min(`report_time`))) / 60) / 60) difference


FROM `your_table` WHERE 1 GROUP BY `Emp_id`, `report_date`

First you group by employee id and report date, that way you get one row for each id on distinct date. Then you select the minimal time for employee on that given date, and also the max time. Then you get the time difference in seconds, divide it by 60 so you get it in minutes, and then divide it again by 60 so you get it in hours.

Community
  • 1
  • 1
helloworld
  • 527
  • 6
  • 21
  • @helloworld..I am fetching data from text file of thumb reader.In that there is no separate field of checkin & checkout. – Shivaraj patil Mar 18 '15 at 04:08
  • Emp_id report_date report_time 11 2014-12-01 08:02:31 21 2014-12-01 08:13:04 11 2014-12-01 18:03:41 21 2014-12-02 16:36:02 @helloworld I am not able find diff if he checkout next day – Shivaraj patil Mar 26 '15 at 04:36
  • I cannot help you. You need to post another question, describing your situation, what code you have used so far, and the problems with it. – helloworld Mar 26 '15 at 15:21
  • I gave it a like so that hopefully more people will see it – helloworld Mar 27 '15 at 05:35