Calculate difference of values for records on outer ends of timeframe

Databases
1

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.


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:


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


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!

2

I solved it!

At least for the most part:


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)