PHP - MySQL time/date calculation

First, an entry in the database:

enter image description here

I have an input form that writes start 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.

This question and answers originated from www.stackoverflow.com
Question by (6/28/2011 4:12:55 PM)

Answer

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

Answer by
Find More Answers
Related Topics  php  mysql  sql  date  time
Related Questions

  • MySQL date/time calculation

    I have a date and time column in my mysql table called start_date and start_time respectively. I want my users the ability to set reminders for themselves to be sent X hours before start_time , min.…

  • dos date/time calculation

    I am working on a project that involves converting data into dos date and time. using a hex editor (Hex Workshop) i have looked through the file manually and and found the values I am looking for, h…

  • PHP date time and mysql

    Possible Duplicate: Change date format (in DB or output) to dd/mm/yyyy - PHP MySQL PHP - Convert to date format dd/mm/yyyy mysql saves my date and times like this... 2011-08-31 …

  • Time Zones in PHP, Date, MySQL...?

    I am trying to figure out how to display time in PST on my php site. I have been putting in timestamps in the MySQL Database with NOW() and calling them like this: date("m/d/y g:i a", strtotime($…

  • Date and Time functions in PHP & MySQL

    I have date and time stored in my database as MySQL DATETIME datatype. While inserting into the database, I am using the following PHP variable $serverTime = strftime("%Y-%m-%d %H:%M:%S",$_SERVER…

  • Tricky PHP/Mysql date calculation

    I have a date such as 2009-06-30 (30th june, 2009). I want to calculate a date which appears 2 months, and 3 days before or after the first date. Or, 3 months, 6 days, before or after, etc. How can …

  • MySQL to return only last date / time record

    We have a database that stores vehicle's gps position, date, time, vehicle identification, lat, long, speed, etc., every minute. The following select pulls each vehicle position and info, but the…

  • How to Convert this date to mySQL time using PHP?

    20101109-240000 or 20101109-235959 How to convert the date format above into MySQL format, Y-m-d H:i:s Thank You

  • how to insert and read time/date in mysql/php?

    i want to read the single day, month and year, without adding 3 extra myql-rows, in this format (with php): day: 01 month: Jan, Feb, Mar..(first three letters) year: 2011 this is table and ph…

  • php mysql time elapsed calculation

    I have an app [iphone], that sends to a server some times [using json], so the times look like hh:mm 24 hour format, the time gets saved in the db as varchar, I need to calculate the elapsed time…