SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 47

Thread: Subtract Dates

  1. #1
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Subtract Dates

    Hi

    I have a database containing dates, is it possible to retrieve all dates within a specific year range, then subtract the retrieved dates from today's date?

  2. #2
    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)
    yes, and you can do this entirely with SQL, no php necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So I don't need PHP for any of it?

    I want to search the DOB (DDMMYYYY) field within a database, for any users that are between the ages specified in a form (between 25 and 50)

    How would I do this?

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select user
         , dob
      from birthdays
     where dob between curdate() - interval 25 years and curdate() - interval 50 years
    http://dev.mysql.com/doc/refman/5.0/...functions.html

  5. #5
    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 F.Danials View Post
    ... retrieve all dates within a specific year range, then subtract the retrieved dates from today's date?
    Code:
    SELECT somedate
         , DATEDIFF(CURRENT_DATE,somedate) AS days_diff
      FROM daTable
     WHERE somedate >= '2005-01-01'
       AND somedate  < '2006-01-01'
    the specific year range is best searched as indicated above (assuming there's an index on somedate)

    this also works, but is inefficient:
    Code:
    WHERE YEAR(somedate) = 2005
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do I return any records contained within a database, if the value of drop down box 1 and drop down box 2 is between the difference of todays date, and the DOB stored in the database?

    I hope this makes sense.

  7. #7
    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 F.Danials View Post
    I hope this makes sense.
    nope, almost but not quite

    perhaps you could show sample values from the dropdowns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you mean?

    drop-down menu 1 contains values 25-99
    drop-down menu 2 contains values 25-99

    Subtract today's date from all DOB dates within the database, in order to return the total years difference. If the total years is between drop-down 1's and drop-down 2's values, then the whole record should be displayed!

    Hope this makes more sense!

  9. #9
    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 something
      FROM daTable
     WHERE DATEDIFF(CURRENT_DATE,DOB) 
           BETWEEN $dropdown1 AND $dropdown2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do I need to replace "CURRENT_DATE" with today's date, or can I use the date() function?

    Code:
    SELECT something
      FROM daTable
     WHERE DATEDIFF(date(),DOB) 
           BETWEEN $dropdown1 AND $dropdown2

  11. #11
    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 F.Danials View Post
    Do I need to replace "CURRENT_DATE" with today's date
    no, because CURRENT_DATE is today's date

    MySQL's Date() function is actually the same as CURRENT_DATE

    however, CURRENT_DATE is Standard SQL, so use that instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK. I now have the following code, but the nothing gets returned from the database.

    I have a record within the database, that contains the DOB 12-05-1984, and drop-down menu 1 contains value "18", and drop-down menu 2 contains value "27".

    As a result of this, the records returned, should be 12-05-1984, bu nothing gets displayed. Why is this? (Do I need to set the format of the date?)

    PHP Code:
    <?php
    require_once "init.php";

    $from mysql_real_escape_string($_POST['from']);
    $to mysql_real_escape_string($_POST['to']);

    echo 
    $from;
    echo 
    $to;

    mysql_select_db($data$con) or die (mysql_error());

    $result mysql_query("SELECT *
      FROM member
     WHERE DATEDIFF(CURRENT_DATE,DOB) 
           BETWEEN 
    $from AND $to");

    while(
    $row mysql_fetch_array($result))
    {
      echo 
    $row['username']."<br />";
    }

    mysql_close($con)
    ?>

  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 F.Danials View Post
    (Do I need to set the format of the date?)
    possibly

    what is the DATATYPE of the DOB column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The DOB Data Type is: Date
    Don't know if this will also help, but the dates are in the following format: YYYY-MM-DD

  15. #15
    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)
    please run this query and show me what you get:
    Code:
    SELECT DATEDIFF(CURRENT_DATE,DOB) AS d  FROM member LIMIT 9
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nothing except the contents of the two drop-down menus (from & to)

    I ran the following code, including your query, but nothing was displayed.

    PHP Code:
    <?php
    require_once "init.php";

    $from mysql_real_escape_string($_POST['from']);
    $to mysql_real_escape_string($_POST['to']);

    echo 
    $from;
    echo 
    $to;

    mysql_select_db($data$con) or die (mysql_error());

    $result mysql_query("SELECT DATEDIFF(CURRENT_DATE,DOB) AS d  FROM member LIMIT 9");

    while(
    $row mysql_fetch_array($result))
    {
      echo 
    $row['Username']."<br />";
    }

    mysql_close($con)
    ?>

  17. #17
    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)
    no, that's not what i meant

    the query i asked you to run in post #15 should return only 1 column of data

    please run it outside of php in a front-end app or the command line
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I ran it in MySQL. The following was returned/displayed:

    d
    14764
    11543
    21596
    8945
    218
    10232
    11983
    10454
    9583

  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)
    do you see what this means? DATEDIFF produces the difference in days

    14764 means the guy is about 40&#189; years old

    so what would you do to change the WHERE clause to be able to use the dropdown values?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    40 years old? - How did you work that out? (14764 / 365?)

    I haven't a clue on what to change so that the WHERE clause includes the drop-down menus - This I defiantly need help on

  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)
    Quote Originally Posted by F.Danials View Post
    40 years old? - How did you work that out? (14764 / 365?)
    you catch on quickly

    Quote Originally Posted by F.Danials View Post
    I haven't a clue on what to change so that the WHERE clause includes the drop-down menus - This I defiantly need help on
    WHERE DATEDIFF(CURRENT_DATE,DOB)/365 BETWEEN ...

    easy, innit?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what would the final code look like? So it only displays records between the ages set by the two drop-down menu values?

  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)
    yes, why don't you try it, again outside of php, substituting numeric literals like 9 and 37 and similar for the two dropdown values

    this experimentation will familiarize you with testing queries

    it should further show you that your php code will also have to ensure that the 2nd value is not less that the 1st (note: it can be equal)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So I would enter the following outside php?: - Correct?

    PHP Code:
    SELECT *
    FROM member
    WHERE DATEDIFF
    (CURRENT_DATE,DOB)/365
    BETWEEN 18 
    AND 27 

  25. #25
    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)
    what happened when you tested that?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •