检索两个日期之间的不同小时数

Question

I would appreciate it if someone could help me with some logic here on how to turn my timestamp function into a working payroll service.

Right now when a user logs in to my site, they can click a clock in button which timestamps the datetime format into SQL.

This is the format: 2013-05-12 09:19:00

• On click, it starts the clock in the in column.
• When they click again, it adds to the out column.

It is storing in these columns:

• id (auto_increment)
• userid (unique)
• in (datetime)
• out (datetime)

How would a function look / work to grab total hours clocked from an employee?

At the least, point me in the right direction to resolve this scenario please.

Answer 1

You can take advantage of the MySQL functions SUM and TIMESTAMPDIFF to calculate it for you:

SELECT SUM(TIMESTAMPDIFF(HOUR, `in`, `out`)) AS hours
  FROM myTable
 WHERE userid = 1

Live DEMO