Jump to content

Problem With Date Calculations


murfitUK
 Share

Recommended Posts

I have a mysql database with records containing dates in the format "yyyy-mm-dd". Each record has a start date and an end date. I need to be able to calculate the number of days between these dates (inclusive). EG 2009-02-01 to 2009-02-01 is 1 day, 2009-02-01 to 2009-02-02 is 2 days, 2008-11-05 to 2008-11-28 is 24 days etc.Everything works fine except towards the end of March!2009-03-01 to 2009-03-28 gives 28 days (good)2009-03-01 to 2009-03-29 gives 29 days (good)2009-03-01 to 2009-03-30 gives 29 days (not good)2009-03-01 to 2009-03-31 gives 30 days (not good)This is the formula I've got (can't remember where I got it from):$days=floor(((strtotime($endDay)-strtotime($startDay))/3600/24)+1);I'm sure it is a php issue rather than the mysql database. The fields are set up using mysql's type "date" with default "0000-00-00" so data is stored simply as "2008-10-27" etc.Has it got something to do with the changeover from GMT to BST? If so, how do I fix it? Is there a better formula?Thanks.

Link to comment
Share on other sites

Make SQL fix it.If you can select the two dates (let's call them date1 and date2 for the sake of the example), you can make MySQL substract them, and convert the result to days, like:

SELECT TO_DAYS(date2 - date1) FROM ...;

Link to comment
Share on other sites

  • 1 month later...
please help, I am totally new here... I'm using your line and getting Null result. What is wrong?SELECT TO_DAYS('2009-04-01' - '2009-04-05');
To put it in simple terms, you're trying to subtract 5 from 1 (1 - 5)You can't do that with dates. When performing the subtraction, the highest date must be on the left and the lowest dat on the right.
Link to comment
Share on other sites

That is right! Thank you so much!Now the next question - How to get this number out ;(I am using this code, but it prints "Array" instead of number. Would you, please, help...$con = mysql_connect("my_db","my_pass", "my_name");if (!$con) die('Could not connect to My-db: ' . mysql_error());mysql_select_db("my_db", $con);$result = mysql_query("SELECT TO_DAYS('2009-04-03') - TO_DAYS('2009-04-01')");while($row = mysql_fetch_array($result)) { echo $row; echo "<br />"; }

Link to comment
Share on other sites

$row is an array, $row[0] would hold the first field. You can also give it a name and refer to it by name.

$result = mysql_query("SELECT TO_DAYS('2009-04-03') - TO_DAYS('2009-04-01') AS num");while($row = mysql_fetch_assoc($result)){  echo $row['num'];  echo "<br />";}

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...