SitePoint Sponsor |
|
User Tag List
Results 1 to 24 of 24
Thread: Calculate Number of Days
-
Jan 16, 2006, 06:53 #1
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Calculate Number of Days
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.
-
Jan 16, 2006, 07:01 #2
- Join Date
- May 2005
- Location
- London
- Posts
- 475
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I would convert the MySQL date format to a Unix Timestamp. That way you can easily calculate the number of days like this:
PHP Code:$days = ceil(($endDate_ts - $startDate_ts)/86400)
-
Jan 16, 2006, 07:18 #3
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Last edited by wbmdan; Jan 16, 2006 at 07:19. Reason: question - 'ts'
-
Jan 16, 2006, 07:22 #4
- Join Date
- May 2004
- Location
- Braga, Portugal
- Posts
- 596
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Or ...
You can use MySQL's DATEDIFF() which allows all MySQL supported date or datetime formats, it'll be simpler.
Code:SELECT ABS(DATEDIFF('2006-01-24', '2006-02-04')) AS `date_diff`;
That will give you the number of days between the two dates, taking into consideration number of days in months, between years, etc.
CheersLast edited by DMacedo; Jan 16, 2006 at 07:51. Reason: typo
~ Daniel Macedo
-
Jan 16, 2006, 07:32 #5
- Join Date
- May 2005
- Location
- London
- Posts
- 475
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)
-
Jan 16, 2006, 18:59 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
unix_timestamps? seconds? why?
use the mysql TO_DAYS function! works even where DATEDIFF doesn't
-
Jan 16, 2006, 19:02 #7
- Join Date
- May 2004
- Location
- Braga, Portugal
- Posts
- 596
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I can't beat Rudy at his own game
If he says it's best, than I'm throwing DATEDIFF out the window~ Daniel Macedo
-
Jan 16, 2006, 20:10 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
DATEDIFF works fine, it's just that it doesn't work in earlier versions, whereas TO_DAYS does
-
Jan 16, 2006, 20:16 #9
- Join Date
- May 2004
- Location
- Braga, Portugal
- Posts
- 596
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Last edited by DMacedo; Jan 17, 2006 at 03:53. Reason: I hate typos
~ Daniel Macedo
-
Jan 17, 2006, 02:06 #10
- Join Date
- Apr 2005
- Posts
- 59
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
check out date functions at http://andymatter.com/
-
Jan 17, 2006, 04:56 #11
- Join Date
- May 2005
- Location
- London
- Posts
- 475
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
-
Jan 17, 2006, 05:20 #12
- Join Date
- May 2004
- Location
- Braga, Portugal
- Posts
- 596
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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~ Daniel Macedo
-
Jan 17, 2006, 05:22 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by Will Kelly
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?
-
Jan 17, 2006, 05:39 #14
- Join Date
- May 2005
- Location
- London
- Posts
- 475
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jan 17, 2006, 07:10 #15
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Need more detail
Originally Posted by r937
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.
-
Jan 17, 2006, 07:38 #16
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:select to_days(endDate) - to_days(startDate) as days_diff
-
Jan 17, 2006, 18:04 #17
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jan 17, 2006, 18:08 #18
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Also tried this...
$today = date("d");
$test = $today - $project['days_diff'];
echo $test;
...isn't returning the correct value.
-
Jan 17, 2006, 18:08 #19
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
the difference between today and the project due date would be:
Code:to_days(projectDueDate) - to_days(current_date)
-
Jan 17, 2006, 18:11 #20
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jan 17, 2006, 18:18 #21
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
CURRENT_DATE is a reserved word in mysql, and contains the -- wait for it -- current date
check the mysql manual for Date Functions
-
Jan 17, 2006, 18:26 #22
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?Last edited by wbmdan; Jan 17, 2006 at 18:42. Reason: showing code from before
-
Jan 17, 2006, 18:43 #23
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jan 17, 2006, 18:53 #24
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks Rudy!
It WORKS...VERY well!
I Appreicate your help.
Bookmarks