SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 31
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Trouble comparing dates in two tables

    I found a bug in my website where the user's most recent login was not being recorded. I discovered this by seeing more recent dates in the searches table compared to what I'm seeing in the users table. The problem I ran into is that the date column in the searches table uses the date and time. The date column in the users table is setup to only show the date. So in the query below I formatted the date in the search column to look like the date column in the users table. Unfortunately, the database still sees the dates in the search column to be greater, simply because there is also a time as part of the date. Can someone help me tweak my query so I can really find the users that have a more recent date in the searches table compared to the users table?

    Thanks!



    PHP Code:
    -- Accounts where the date in search table is more recent than the users table.
    SELECT
       u
    .date,
       
    u.uID,
       
    u.firstname,
       
    u.visited,
       
    u.last as lastLogin,
       
    date_format(s.date'%Y-%m-%d') as searchDate
     FROM users u
    INNER
     JOIN searches s
       ON u
    .uID s.author
    WHERE
      s
    .date u.last
    GROUP BY
     u
    .uID
    ORDER BY u
    .last
      limit 100

    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    remove the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, I took out that line and the query runs fine. But I'm still stumped on how to resolve the fact that the searches table is using full dates, including the time, and the users table is just storing dates as 2011-05-12. Because of this difference, the query is showing a lot of rows where it thinks the date in the searches table is more recent, I'm guessing because the time is included. Should I do an update on the users table and convert all of the last login dates to use the kind of date with time? Or is there a better way to get around this?

    Once I can get this worked out, then I plan on using the following query to fix this mess. Does this look good to you?

    PHP Code:
    UPDATE users usearches s
    SET u
    .last s.date
    WHERE s
    .date=(select date from searches where author =u.uID order by date desc limit 1) AND
    s.date u.last AND
    u.uID s.author 
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    Should I do an update on the users table and convert all of the last login dates to use the kind of date with time? Or is there a better way to get around this?
    i don't think you've explained the problem adequately enough for me to offer an opinion

    for starters, why are there two date/datetime columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    One date column is used in the users table. This records the last login date. The other datetime column is in the searches table. It records when a search was conducted by the website user.
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, those are good reasons for separate columns

    and you're saying that they're out of sync?

    have you fixed the source of the problem yet?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, they are out of sync. I found a bug in my website code where the last login date was not being updated in the users table. No telling how long that bug has been there. I have since fixed in and now it updates when people login. So the goal is to find their most recent visit date based on the searches they have conducted. Then copy the date of the search over into the users table.

    Thanks!!
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, i'll walk you through it, we're going to do this in two steps

    the first step is to write a query which gets the most recent search date for each user
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, here is the one I've been working on today that will do that very thing:

    PHP Code:
    -- Accounts where the date in search table is more recent than the users table.
    SELECT
      u
    .uID,
      
    u.date as registerDate,
      
    u.firstname,
      
    u.last as usersVisit,
      
    date_format(s.date'%Y-%m-%d') as searchesVisit
    FROM users u
    INNER
    JOIN searches s
      ON u
    .uID s.author
    WHERE
     s
    .date=(select date from searches where author =u.uID order by date desc limit 1) AND
     
    s.date u.last
    ORDER BY u
    .uID
     limit 100

    Convert your dollars into silver coins. www.convert2silver.com

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, that's not what i was after, i wanted just the most recent search date for each userid, it's just a simple one-table query using the searches table

    combining it with the users table was step 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, here you go:

    select author, date from searches group by author order by author;
    Convert your dollars into silver coins. www.convert2silver.com

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that's almost right, but you're missing the MAX function (and ORDER BY doesn't matter here)
    Code:
    SELECT author
         , MAX(date) AS latest
      FROM searches
    GROUP
        BY author
    the next step is to join this subquery to the users table

    ever done that? join a subquery to a table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Here is what I came up with. Remember, however, that s.date is a datetime and u.last is just a date. So I'm wondering how we will proceed next to accurately campare the two. Also, why is MAX used in this case when grouping it by author should only return one row per user.

    SELECT s.author
    , u.firstname
    , u.last
    , MAX(s.date) AS searchDate
    FROM users u
    INNER JOIN
    searches s
    on u.uID = s.author
    GROUP
    BY author;
    Convert your dollars into silver coins. www.convert2silver.com

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    MAX is used because you want the latest value of the datetime, i.e. the largest value

    if you do this --
    Code:
    SELECT author, date FROM searches GROUP BY author
    then the value of the date column that is returned for each author is indeterminate -- it could be any of the values for each author

    since you want the latest one, you must do this --
    Code:
    SELECT author, MAX(date)  FROM searches GROUP BY author
    see the difference?

    as for step 2, i don't think you understood what i was saying about joining to the subquery...
    Code:
    SELECT ...
      FROM users u
    INNER 
      JOIN ( SELECT author
                  , MAX(date) AS latest
               FROM searches 
             GROUP 
                 BY author ) AS s
        ON s.author = u.uID
    see how the subquery is used, in the FROM clause a subquery is called a derived table

    play with that for a minute, and convince yourself it's working correctly, then we'll proceed to the update
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Rudy, how come when I run the following query I get an error that says, "Unknown column s.date in field list"? I wanted to not only show the date of their last login (u.last) but also the date of the most recent search (s.date)

    SELECT u.uID
    , u.last
    , s.date
    FROM users u
    INNER
    JOIN (SELECT author, MAX(date) AS latest FROM searches GROUP BY author ) AS s
    ON s.author = u.uID;
    Convert your dollars into silver coins. www.convert2silver.com

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    in this query, the table alias "s" refers to the subquery, not to the searches table

    the subquery has only two columns, author and latest

    that's why you got the error message

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

  17. #17
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, I got the query fixed by changing latest to date.

    SELECT u.uID
    , u.last
    , s.date
    FROM users u
    INNER
    JOIN (SELECT author, MAX(date) AS date FROM searches GROUP BY author ) AS s
    ON s.author = u.uID
    WHERE
    s.date > u.last;
    I also added a check in the WHERE clause. The problem is that the s.date is a datetime and u.last is just a date. So the query is returning incorrect results thinking that dates are greater when they really aren't. As you can see from the top of this thread, I tried to convert the datetime to just a date using "date_format(s.date, '%Y-%m-%d') as searchDate" but that didn't work.

    Before we can move on to the update, I gotta figure out what I'm doing wrong with the date compare.

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    Ok, I got the query fixed by changing latest to date.
    you shoulda changed it the other way around -- it's still the same query, but the intent of what you're doing is clearer
    Code:
    SELECT u.uID
         , u.last
         , s.latest
      FROM users u
    INNER 
      JOIN ( SELECT author
                  , MAX(date) AS latest
               FROM searches 
             GROUP 
                 BY author ) AS s
        ON s.author = u.uID
     WHERE s.latest > u.last
    Quote Originally Posted by busboy View Post
    The problem is that the s.date is a datetime and u.last is just a date.
    that's not a problem, and you don't need to do any conversion

    please show a few rows of results which you think are wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The first row:

    uID = 121
    last = 2011-03-10
    latest = 2011-03-10 06:56:26

    So the query thinks that somehow 2011-03-10 06:56:26 is greater than just 2011-03-10. Make sense?
    Convert your dollars into silver coins. www.convert2silver.com

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    So the query thinks that somehow 2011-03-10 06:56:26 is greater than just 2011-03-10. Make sense?
    of course it does

    2011-03-10 is the same as 2011-03-10 00:00:00 (midnight on the morning of the day)

    and 2011-03-10 06:56:26 ~is~ greater than 2011-03-10 00:00:00 (almost 7 hours past midnight)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ugh. So am I screwed? How come "date_format(s.date, '%Y-%m-%d') as searchDate" doesn't work?
    Convert your dollars into silver coins. www.convert2silver.com

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    Ugh. So am I screwed?
    why do you say that?

    the query is working correctly, and it's telling you when there are searches rows with a later datetime than the user's last login

    isn't that what you wanted?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, I guess you're right. Now we just need to update the 3,000+ rows in the users table. You have a trick up your sleeve so that we can take a datetime from the searches table and update the users table with just a date? It's amazing how complex these databases can be.
    Convert your dollars into silver coins. www.convert2silver.com

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    first thing i think you need to do is change the last column in the users table from date to datetime

    you mentioned that you fixed the problem in the application that was not updating this correctly, right?

    can you change it to update a datetime instead?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, I did a little bit of testing and determined that changing the field to a datetime has no affect on users logging in, nothing crashes. Fire when ready with the update portion.

    Thanks.
    Convert your dollars into silver coins. www.convert2silver.com


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
  •