How to: sum timestamp from same column using another column as a reference

Question

question

hollowspy.drl asked Jun 1, '18

How to: sum timestamp from same column using another column as a reference

Hi Everyone,

I have a problem at hand

I need to calculate the total time per day, per month for each employee based on their attendance(time in and time out).

I am using a table with 4 columns

id(pk),rfid_no, att_date(timestamp),status(0 for in and 1 for out)
each time the rfid_no is entered the timestamp is registered and the status has to show in and out

I am still working on the PHP code to automate the status.

If you have any input on that please help as well.

The RFID_No is like the emp_id

below is my query for finding the total working hours.

I also need the total working days.

SELECT c.firstname,a.rfid_no, FLOOR(sum(unix_timestamp(b.att_date)- unix_timestamp(a.att_date))/3600) as working_hours FROM timesheet a
join users c on c.rfid_no = a.rfid_no
join timesheet b on (a.rfid_no = b.rfid_no && a.status = 0 && b.status = 1 )where (a.id+1) = b.id

It returns only one rfid_no and the total time is completely wrong.

Please help!!!! Thank you in advance.

if you need more input please ask.

sql statistics sum inner join timestamp

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WRBI commented · Jun 01, 2018 at 01:38 PM

Don't know if it's it because it's late on Friday and my brain is frazzled but I'm not sure that this is the best design for the following reasons:

What if some leaves and doesn't sign out or vice versa, won't this mess up your joins because there will be no logged in / logged out status. We've all worked in buildings where people tail gate through doors with card scanners.
I can't even think of at this moment how to get the correct matching record for someone. I don't a.id + 1 = b.id cuts it. Surely if anyone logs in / out between the event of person 1 then it wont work. (like i say my brain is frazzled so maybe it does work.

I think having, id (pk), rfid, LoggedInTime, LoggedOutTime is a much better design because you can simply find the last record where they havent logged out and set it. Getting the amount of time is then just a DateDiff() - also you have the flexibility to correct the logged in / logged out if that person is tail gating or whatever!

That's just my two cents. Maybe someone else has a better solution!

0 ·

WRBI commented · Jun 01, 2018 at 01:43 PM

Forgot to say - attaching some sample data that you already might help someone come up with a solution.

0 ·

question