#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    388
    Rep Power
    47

    Date calculation in query


    Hi

    I have a database table full of events and dates and I'm trying to find the last occurrence and age of each event. (Age is obviously the time elapsed since the last occurrence)

    So, I have the following query:

    SELECT max(date) as last,
    min(curdate()-date) AS age
    FROM event_data
    WHERE event = $event
    GROUP BY event"
    (This Query returns one row of data within a WHILE loop for every event. I think the "GROUP BY" is unnecessary)

    A partial list of the results are as follows:

    Event - Count - Last - Age
    56 - 15 - 2012-10-27 - 191
    59 - 13 - 2012-11-17 - 101
    44 - 13 - 2012-12-15 - 3
    29 - 13 - 2012-11-28 - 90
    7 - 12 - 2012-12-08 - 10
    5 - 12 - 2012-11-28 - 90

    (the "Count" field is the number of occurrences coming from another query)

    Today's date is 2012-12-18. For some reason, all of the December ages are correct but the older ones are WAY off.

    What have I done wrong?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    So this table also stores dates in the future?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    388
    Rep Power
    47
    Originally Posted by cafelatte
    So this table also stores dates in the future?
    Umm... no, I don't think so. The latest date is 2012-12-15, accurately shown as three days ago.

    Am I misreading something?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Code:
    SELECT event
         , MAX(date) date
         , DATEDIFF(CURDATE(),MAX(date)) age 
      FROM event_data 
     GROUP 
        BY event;
    your query subtracts (for instance) the integer 20121101 from 20121208 which (in this case) equals 107.

IMN logo majestic logo threadwatch logo seochat tools logo