MySQL time/date calculation

First, an entry in the database:

I have an input form that writes st开发者_Python百科art date, start and end times (in hour and minute) of working days plus lunch break in minutes (in the example dato=date, modetime=start hour, modeminut=start minute, fyrtime=end hour, fyrminut=end minute). I need to do several calculations:

• First calculate the date, start hour and minute into the datetime field modetid.
• The do a similar calculation with the end hours and minutes, but move the date up one day if end hours is less than start hour (lets call it fyrtid)
• And finally calculate the difference between fyrtid and modetid minus the pause minutes.

Can it be done directly and automatically in the database (if yes, how) or do I need some PHP to do it (and again, if yes, how)?

I know its a tall order but I have not been able to find much information on date/time calculations that made much sense on my low level of knowledge. Any link to a comprehensive guide on date/time calculation in MySQL or PHP would also be greatly welcomed.

---

I suggest you to work by php function time() it's based on unix timestamp ( like UNIX_TIMESTAMP() in Mysql ) unix time is like this : 1307387678. Use a calender in your form for start time also for your end time. put a facility what clients could select time of day ( hour and minutes ) then covert those fileds by strtotime() to unix timestamp like following code ( set date format to mm/dd/yyyy ) :

$startdate = strtotime ( "$_POST['calender_start'] $_POST['hour_start']:$_POST['minutes_start']"  );
$enddate = strtotime ( "$_POST['calender_end'] $_POST['hour_end']:$_POST['minutes_end']"  );

Change your db table fields to : cpr,startDate,endDate,pause,basked,.....

it's so flexible. while you want to fetch special recorde you could fetch rows by this sql :

SELECT ...... FROM [TABLE_NAME] WHERE [VALUE] BETWEEN startDate AND endDate 

I hope it be usefull for you