SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Calculate difference of values for records on outer ends of timeframe

    Hy there,

    I'm working on a problem I can't seem te solve in MySQL.

    I have a table with snapshots of cummulative values. The information I need is the difference of those values within a timeframe.
    At the moment I use PHP to calculate thoses difference because I can't seem te get the min and max date from the table AND the values corresponding to those records.

    Code:
    SELECT fms1.* 
    FROM fms1 
    WHERE
    AND fms1.gps_unit_id = 114 
    AND fms1.created >= "2013-03-01 00:00:00" 
    ORDER BY fms1.created ASC 
    LIMIT 1
    ) UNION (
    SELECT fms1.* 
    FROM fms1 
        WHERE fms1.gps_unit_id = 114 
        AND fms1.created >= "2013-03-01 00:00:00" 
        AND fms1.created <= "2013-03-01 23:59:59" 
        ORDER BY fms1.created DESC 
    LIMIT 1
    )
    This way I get two rows returned for which I calculate the difference for each of those values. (say fms1.odometer, fms1.idle_time and so on)

    I tried something like this:

    Code:
    SELECT
        fms1.*,
        max(fms1.created) AS MaxDate,
        min(fms1.created) AS MinDate
    FROM fms1
    WHERE fms1.created BETWEEN "2013-03-01 00:00:00" AND "2013-03-01 23:59:59" 
    AND fms1.gps_unit_id = 114
    This does return the correct MinDate and MaxDate for the records on that day (over a 100), but it only returns the complete record for the MinDate. Which in this case is the correct one. You cannot sort the records by id, because causality is not guarantied.

    The thing I can't get my head around is how to select complete records based on the outcome of funtions like min() and max(). For instance if you use
    Code:
    SELECT
        max(fms1.created) AS MaxDate,
        min(fms1.created) AS MinDate
    FROM fms1
    WHERE fms1.created BETWEEN "2013-03-01 00:00:00" AND "2013-03-01 23:59:59" 
    AND fms1.gps_unit_id = 114
    I do get the correct dates, but how to get the records belonging to those values? You can't use min() and max() in subqueries...

    Many thanks!
    What good are one-liners if they don't
    fit.

  2. #2
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I solved it!

    At least for the most part:
    Code:
    SELECT SQL_NO_CACHE
        (b.odometer - a.odometer) AS diff_odo
    FROM fms1 AS a, (
        SELECT 
            max(fms1.created) AS MaxDate, 
            min(fms1.created) AS MinDate 
        FROM fms1 
        WHERE fms1.created BETWEEN  "2013-03-01 00:00:00" AND "2013-03-01 23:59:59" AND fms1.gps_unit_id = 114
    ) AS t
    INNER JOIN fms1 AS b ON b.created = t.MaxDate AND b.gps_unit_id = 114
    WHERE (a.created = t.MinDate) AND a.gps_unit_id = 114
    Giving everything an alias and joining the table with itself gives you a way to do this. With an index on both date and gps_unit_id it is also superfast.

    The only thing I still can't figure out is how to select the id's from the rows in the subquery. So I don't want to find the value of the max and min date, but the Id belonging to that record. That would remove the need to check on gps_unit_id in the join and where clause of the outher query (and a nice trick to have up your sleeve anyway)
    What good are one-liners if they don't
    fit.


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
  •