php - Advanced Timestamp Calculations (Even and Odd Rows Totalling) -

i creating personal time card system pet project , have down pat except totaling of times each day , week. let's have 4 punches day pulled database using while loop:

[1] 2013-03-01 07:00:00 - in - (for day) [2] 2013-03-01 12:00:00 - out (to lunch) [3] 2013-03-01 12:30:00 - in (from lunch) [4] 2013-03-01 15:30:00 - out (for day) 

my question how go creating running total spit out total of 8 hours? have thought days no logical solution in mind. in advance!

you should probably change structure of table. underlying idea notion of working period, requires start time , end time. current structure splits fact 2 rows. use 1 row instead.

work_periods time_in              time_out -- 2013-03-01 07:00:00  2013-03-01 12:00:00 2013-03-01 12:30:00  2013-03-01 15:30:00 

if have table this, can difference in minutes timestampdiff().

select time_in, time_out,      timestampdiff(minute, time_in, time_out) elapsed_time work_periods; 

you can return same data existing table. think should temporary approach. kind of structure makes hard identify "in"s missing "out", , "out"s missing "in".

create table work_periods (   work_ts datetime not null,   in_or_out varchar(5) not null,   primary key (work_ts)   );  insert work_periods values ('2013-03-01 07:00:00', 'in'), ('2013-03-01 12:00:00', 'out'), ('2013-03-01 12:30:00', 'in'), ('2013-03-01 15:30:00', 'out'); 

the "out" goes each "in" earliest "out" that's later "in".

select wp1.work_ts time_in,        (select min(wp2.work_ts)          work_periods wp2         wp2.work_ts > wp1.work_ts           , wp2.in_or_out = 'out') time_out work_periods wp1 wp1.in_or_out = 'in'; 

if had choice, i'd

• build updateable view last select statement,
• update application code use view, , finally
• replace view base table @ beginning of answer.