SitePoint Sponsor

User Tag List

Results 1 to 24 of 24
  1. #1
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    North America
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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.

  2. #2
    SitePoint Evangelist Will Kelly's Avatar
    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
    btw use 'UNIX_TIMESTAMP(value) AS timestamp' in MySQL to automatically get a timestamp from a date or datetime field.

  3. #3
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    North America
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    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 06:19. Reason: question - 'ts'

  4. #4
    SitePoint Evangelist DMacedo's Avatar
    Join Date
    May 2004
    Location
    Braga, Portugal
    Posts
    596
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool 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`;
    (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
    Last edited by DMacedo; Jan 16, 2006 at 06:51. Reason: typo
    ~ Daniel Macedo

  5. #5
    SitePoint Evangelist Will Kelly's Avatar
    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 )

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    unix_timestamps? seconds? why?

    use the mysql TO_DAYS function! works even where DATEDIFF doesn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist DMacedo's Avatar
    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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist DMacedo's Avatar
    Join Date
    May 2004
    Location
    Braga, Portugal
    Posts
    596
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    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 02:53. Reason: I hate typos
    ~ Daniel Macedo

  10. #10
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    check out date functions at http://andymatter.com/

  11. #11
    SitePoint Evangelist Will Kelly's Avatar
    Join Date
    May 2005
    Location
    London
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    unix_timestamps? seconds? why?
    I reserve the right to use a PHP solution in the PHP forum.

  12. #12
    SitePoint Evangelist DMacedo's Avatar
    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

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Will Kelly
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist Will Kelly's Avatar
    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.

  15. #15
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    North America
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Need more detail

    Quote Originally Posted by r937
    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.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select to_days(endDate) - to_days(startDate) as days_diff
    TO_DAYS is kinda like UNIX_TIMESTAMP, except it's in days
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Addict
    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.

  18. #18
    SitePoint Addict
    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.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Addict
    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.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    North America
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    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 17:42. Reason: showing code from before

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Addict
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •