SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How do I get the time difference from a MySQL datetime field

    Hello,

    How does one get the time difference in Hours from a MySQL date+time value which is kept in a datetime field?

    To be exact, we have a field called:
    date_added which is of datetime type

    What we want to be able to do is:

    SELECT id FROM xyz WHERE (Time_now is 6 Hours later than date_added) AND email_sent = 'no'

    Of course "Time_now is 6 Hours later than date_added" is not MySQL
    Just what we are trying to achieve.


    Regards,

    Anoox search engine volunteer

    www.anoox.com

  2. #2

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    To be exact...
    how convenient that you should use that word, "exact"

    surely you want to return rows where date_added is more than 6 hours ago... not exactly 6 hours 0 minutes and 0 seconds ago

    right?

    p.s. cpradio, i prefer to avoid that particular web site... see http://w3fools.com
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,220
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    p.s. cpradio, i prefer to avoid that particular web site... see http://w3fools.com
    It was just the first result from google that showed how to properly use DATE_ADD, so I stopped searching

    To help rectify my miss-guidance, here is the link to mysql's webpage describing date_add
    http://dev.mysql.com/doc/refman/5.5/...ction_date-add

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Just more than 6 Hours ago and not exactly 6 hours 0 minutes and 0 seconds ago.
    But just X hours have passed since the entry of that record.

    Anoox search engine volunteer

    www.anoox.com

  6. #6
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This does not work AT ALL.

    To be exact since I am interested in Hours and not DAYS which is what that link samples,

    I then issued:

    SELECT * FROM qal_history
    WHERE DATE_ADD(date_added, INTERVAL 6 HOUR)
    ORDER BY id DESC LIMIT 10;

    And in returns entries that were added 1 minute ago!

    SELECT * FROM qal_history
    WHERE DATE_ADD(date_added, INTERVAL 1 DAY)
    ORDER BY id DESC LIMIT 10;


    Still brings back results from 1 minute ago!

    Anoox search engine volunteer

    www.anoox.com

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT id 
      FROM xyz 
     WHERE date_added < CURRENT_TIMESTAMP - INTERVAL 6 HOUR
    look ma, no DATE_ADD function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    Still brings back results from 1 minute ago!
    you did not compare the result of the function to anything

    so as long as it's not zero, it evaluates as TRUE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,220
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT id 
      FROM xyz 
     WHERE date_added < CURRENT_TIMESTAMP - INTERVAL 6 HOUR
    look ma, no DATE_ADD function
    Nice.

    Quote Originally Posted by WorldNews View Post
    SELECT * FROM qal_history
    WHERE DATE_ADD(date_added, INTERVAL 6 HOUR)
    ORDER BY id DESC LIMIT 10;
    I would think this is because you are not comparing it to anything... whereas, I would think the below would have worked (although I do like r937's solution better)
    Code:
    SELECT * FROM qal_history
    WHERE CURRENT_TIMESTAMP >= DATE_ADD(date_added, INTERVAL 6 HOUR)
    ORDER BY id DESC LIMIT 10;

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    remember that when you apply a function to a column, mysql cannot use an index on that column

    so instead of this --
    Code:
    WHERE CURRENT_TIMESTAMP >= DATE_ADD(date_added, INTERVAL 6 HOUR)
    you should do this --
    Code:
    WHERE date_added < DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -6 HOUR)
    or preferably the interval arithmetic (non-function) version for increased clarity
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Your suggestion works just fine as far as generating the list. ThanX.

    But how do we display the time added Difference in Hours & Days?

    So what I getting at is when we get the list of Queries which are at least 6 Hours old, they can be some 2 week ago posted, 3 days ago posted, 12 Hours ago posted, etc.

    How do we get then from the date_added values of these queries the following info:

    This record is:
    1 week, 3 Days, 2 Hours old
    or
    1 Day, 12 Hours old,

    etc.

    ThanX,

    Anoox search engine volunteer

    www.anoox.com

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you might try the TIMEDIFF function, although i'm not sure it will express a value that's several weeks old -- test it and see

    alternatively, you can use this --
    Code:
    SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(date_added) AS secs_diff ...
    and then translate the seconds difference into those lovely words like "1 week, 3 Days, 2 Hours" using your front-end language (php or whatever)
    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
  •