Search code examples
phpmysqlforeachmodx

Calculate pay periods based on 14 day pay period - PHP/SQL

I have a database that collects user input on their time in/out. I am collecting this information like so:

user_id     clock_in    clock_out
612         1383710400  1383728400
612         1384315200  1384333200
508         1383710400  1383738400

While looping, I calculate the hours of each object like so:
$total=(($e->clock_out-$e->clock_in)/60/60);

Where I am stuck I'm a bit stuck on how to group them into pay periods. I know that there are 26 pay periods in a year. I also know our starting pay period for this year was 01.13.14 - 01.26.14.

What I have tried: I thought to try just gathering the week of the year date('W',$e->clock_in) while looping through the database results but I am not sure how to group them.

Question: Is grouping/sorting possible while looping during a foreach? Do you suggest a different approach on grouping/sorting the data into pay periods?

Please let me know if more code is needed, etc. Thank you!

Solution

  • Assuming those are standard Unix timestamps, you can trivially extract/mangle dates any way you want. If your workweek corresponds to a standard calendar week, it's even easier:

    SELECT user_id, clock_in-clock_out
FROM yourtable
GROUP BY WEEK(FROM_UNIXTIME(clock_in))

    This will just group by single weeks, but this is just to give you a general idea.

    MySQL has a very wide variety of date/time functions that can be used for this: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week