Calculate Number of Days

[wbmdan]

Hi all,

I have dates coming from MySQL in this format:

yyyy-mm-dd (2006-01-16)

There are two dates 'startDate' and an 'endDate'. I need to be able to calculate the number of days between. For example, event is happening in 'x' days.

Now.... I could simply minus the startDate 'mm' from the endDate 'mm' but this won't work if the dates overlap months.

For example - 2006-01-24 and 2006-02-04. And another consideration is overlapping years as well.

Is there a function or something that can do this?

Thank you.

[Will Kelly]

I would convert the MySQL date format to a Unix Timestamp. That way you can easily calculate the number of days like this:

$days = ceil(($endDate_ts - $startDate_ts)/86400) 

btw use 'UNIX_TIMESTAMP(value) AS timestamp' in MySQL to automatically get a timestamp from a date or datetime field.

[wbmdan]

Thank you Will.

So $days will return the exact number of days even if there are overlapping months and / or years?

Also, what does the '_ts' part mean (on the end of the date fields)?

In the second part of your post are you referring to the MySQL "SELECT * FROM ...." query:?

'UNIX_TIMESTAMP(value) AS timestamp'

Thank you.

[DMacedo]

You can use MySQL's DATEDIFF() which allows all MySQL supported date or datetime formats, it'll be simpler.

  SELECT ABS(DATEDIFF('2006-01-24', '2006-02-04')) AS `date_diff`;

(Absolute value ABS() function is because the return value is a negative when the 1st argument is earlier)

That will give you the number of days between the two dates, taking into consideration number of days in months, between years, etc.

Cheers

[Will Kelly]

Yes (well remove the ceil to get exact).

basic query would be "SELECT UNIX_TIMESTAMP(value) AS timestamp FROM table".

I tend to try and avoid doing lots of calculations within MySQL, it can get confusing at to what should be done where. Especially if someone else needs to read your code. (but thats just my own insignificant opiniion )

[r937]

unix_timestamps? seconds? why?

use the mysql TO_DAYS function! works even where DATEDIFF doesn't

[DMacedo]

I can't beat Rudy at his own game

If he says it's best, than I'm throwing DATEDIFF out the window

[r937]

DATEDIFF works fine, it's just that it doesn't work in earlier versions, whereas TO_DAYS does

[DMacedo]

Thanks for clarifying, Rudy.

Currently even 4.x are growing outdated it's a matter of time until there's no issue in using these kinds of newer functions.

[cyrenity]

check out date functions at http://andymatter.com/

[Will Kelly]

unix_timestamps? seconds? why?

I reserve the right to use a PHP solution in the PHP forum.

[DMacedo]

Will Kelly

You should try to find the best solution (both in performance and ease of use). I believe DATEDIFF() would be more efficient and easy to use than using unix timestamp for calculations.

Oh.. And TO_DAYS() more compatible

[r937]

I reserve the right to use a PHP solution in the PHP forum.

i understand -- i even agree!!

however, don't take it too far, eh?

don't return an entire table in order to count the rows in php, use SELECT COUNT(*) instead

that might seem a trivial example, but now consider returning rows from a transaction table where the transaction occurred in the afternoon -- how would you do that?

[Will Kelly]

Don't worry I wouldn't take my point too far, mainly I'm just covering up my inexperience with using the MySQL date format, as I was spoon fed on using unix timestamps instead.

[wbmdan]

unix_timestamps? seconds? why?

use the mysql TO_DAYS function! works even where DATEDIFF doesn't

Hi Rudy,

Thanks for the post.

Can provide an example of how the TO_DAYS function would be used with two table fields called:

startDate
endDate

I need to know the *difference* (in terms of days) between the field values listed above.

Also, I need to be certain that this will work for overlapping months and / or years.

Thank you.

[r937]

select to_days(endDate) - to_days(startDate) as days_diff

TO_DAYS is kinda like UNIX_TIMESTAMP, except it's in days

[wbmdan]

Hi Rudy,

Using this code as noted in your last post seems to produce the difference between the two dates:

to_days(projectDueDate) - to_days(projectStartDate) as days_diff

...which is great! except I need to know the differenc between today and the projectDueDate (maybe that wasn't clear...anyhow..) how can this be done?

I already tried:

$today = date("Y-m-d");

$daysRemaining = $today - $project['days_diff'];

echo $daysRemaining;

but the result was '2002' - the dates in MySQL match the format of $today above 'Y-m-d'.

Appreciate the help.

[wbmdan]

Also tried this...

$today = date("d");

$test = $today - $project['days_diff'];

echo $test;

...isn't returning the correct value.

[r937]

the difference between today and the project due date would be:

to_days(projectDueDate) - to_days(current_date)

[wbmdan]

how do you 'set' current_date (is this in the database or a variable?

Should 'current_date' include the full date - i.e. - 'Y-m-d' or just 'd' for day?

Thanks Rudy.

[r937]

CURRENT_DATE is a reserved word in mysql, and contains the -- wait for it -- current date

check the mysql manual for Date Functions

[wbmdan]

Thanks for the post. I took a look at mysql date functions in the manual.

projectDueDate is set to '2006-01-20' in MySQL (yyyy-mm-dd).

my code reads as follows:

<b>to_days(projectDueDate) - to_days(current_date) as days_diff</b>

but no value is returned...before I was getting a returned value (see below...) - just that it was incorrect.

Previous code which returned the difference between the calucations:

<b>to_days(projectDueDate) - to_days(projectStartDate) as days_diff</b>

Any thoughts on this?

[r937]

try your query outside of php

projectDueDate is a column name, right?

if it gives you an error outside of php, please show the query

[wbmdan]

Thanks Rudy!

It WORKS...VERY well!

I Appreicate your help.