SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DATE_SUB returning incorrect rows

    Hey,

    I have a table which I'm selecting based on a date_sub:

    Code MySQL:
    SELECT private_messages.toID,
                            private_messages.fromID,
                            private_messages.currentDate,
                            private_messages.type,
                            customers.name,
                            customers.lname
                     FROM private_messages
                     INNER JOIN customers ON customers.id = private_messages.fromID
                     WHERE toID =70
                     AND private_messages.currentDate < DATE_SUB(CURRENT_TIMESTAMP , INTERVAL 5 DAY)
                     AND private_messages.currentDate > DATE_SUB(CURRENT_TIMESTAMP , INTERVAL 6 DAY

    Which returns:

    70 167 2009-12-30 09:21:20 PM Ben L
    70 102 2009-12-30 10:20:01 PM Jay Esco
    70 102 2009-12-30 10:37:09 PM Jay Esco
    70 102 2009-12-30 10:37:15 PM Jay Esco
    70 102 2009-12-30 10:37:17 PM Jay Esco
    70 102 2009-12-30 10:37:53 PM Jay Esco

    When I change the INTERVAL to

    Code MySQL:
    AND private_messages.currentDate < DATE_SUB(CURRENT_TIMESTAMP , INTERVAL 4 DAY)
    AND private_messages.currentDate > DATE_SUB(CURRENT_TIMESTAMP , INTERVAL 5 DAY

    It returns

    70 167 2009-12-30 09:21:20 PM Ben L

    Which is also part of the 5 - 6 INTERVAL. So in essence its duplicating the data. What I'm trying to accomplish is to grab from a certain day and only that day (nothing before nothing afterwards).

    I'm not sure where I'm going wrong?

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I also tried

    Code MySQL:

    It always returns zero rows, no matter what interval I assign to it.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
        private_messages.currentDate < CURRENT_DATE - INTERVAL 5 DAY
    AND private_messages.currentDate >= CURRENT_DATE - INTERVAL 6 DAY
    Important things here are being aware of the difference between date and datetime types, and properly bounding the comparison.

    For example, do
    select DATE_SUB(CURRENT_TIMESTAMP , INTERVAL 4 DAY)

    Notice the time component. Now think about what result that would give. It would change depending on the time of day that you issue the query.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by YBH305 View Post
    I also tried

    Code MySQL:

    It always returns zero rows, no matter what interval I assign to it.
    that's correct, this will ~always~ return zero rows

    the reason is because of how BETWEEN works

    when you use BETWEEN, you ~must~ put the lower value first

    here's how it works...

    today is 2010-01-05, so yesterday is 2010-01-04

    your BETWEEN statement would be --
    Code:
    WHERE currentDate BETWEEN '2010-01-05' AND '2010-01-04'
    BETWEEN is always evaluated as two greater/less-than-or-equal comparisons, so this is equivalent to --
    Code:
    WHERE currentDate >= '2010-01-05' 
      AND currentDate <= '2010-01-04'
    and it's impossible for a single currentDate value to be both >= 5th and <= 4th at the same time!!

    make sense now?

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

  5. #5
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for the late response I wasnt able to log in for some time.

    Yes I get it, thanks


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
  •