I want to select the first and last record within a time period (by created field) for each unit (left join from other table) how does that work?
the table with FMS1 data of many units
has many fields including id, gps_unit_id, created, and a bunch of data fields
a gps_units table has
id and user_id
so for one unit I would do:
(
SELECT `fms1`.`gps_unit_id` , `total_fuel` , `engine_hours` , `fms1`.`created`
FROM fms1
WHERE `fms1`.`gps_unit_id` =76
AND `fms1`.`created` >= '2012-03-26 00:00:00'
ORDER BY `fms1`.`created` ASC
LIMIT 1
)
UNION (
SELECT `fms1`.`gps_unit_id` , `total_fuel` , `engine_hours` , `fms1`.`created`
FROM fms1
WHERE `fms1`.`gps_unit_id` =76
AND `fms1`.`created` <= 'NOW'
ORDER BY `fms1`.`created` DESC
LIMIT 1
)
But I want it for all units from a user.
So I started with
SELECT `fms1`.`gps_unit_id` , `total_fuel` , `engine_hours` , `fms1`.`created`
FROM fms1
LEFT JOIN gps_units AS GpsUnit ON GpsUnit.id = fms1.gps_unit_id
WHERE GpsUnit.user_id = 61
AND `fms1`.`created` >= '2012-03-26'
GROUP BY `fms1`.`gps_unit_id`
But if I change the created clause to <= ‘NOW’ It somehow gets all units and returns just te very first recors. I can’t figure out how to sort them the other way round or how to make the correct join.
Any ideas?
(Actually I need to calculate the difference between the to times, so if MySQL could do that in one go, that would be sublime!)
i understand earliest and latest, but do you need the entire record, i.e. the whole row?
or do you just need the created datetime from the earliest and latest row? per unit of course
remember, you said “Actually I need to calculate the difference between the to times”
seems to me like retrieving the entire record (which would involve extra subqueries) is unnecessary, if you can simply get the earliest and latest datetime values
Ah sorry for the inconvience. I hope this makes it all clear:
The table ‘fms1’ gets filled with a new record by every unit about every minute. For a few datafields in each record I want to calculate the difference (the actual increase) within a given time period (last week, or the current week, …).
Ofcourse selecting the fields and all the records in the timeperiod is easy (using BETWEEN on the created field). But then I would just discard 99% of the data I just retrieved (every record besides the first and the last one in the result set). I was hoping MySQL could solve this for me. It’s not nessesary for mysql to calculate the difference between the fields in the records, but hey, if it can ADD and COUNT, maybe there’s way to do this… I would be very happy if i would get 2 records (the first and last in the given time period) for every unit of a user. If those records are complete rows that’s fine, but I’m going to use only 4 of the 14 fields, so a subset makes the whole thing even better (if thats actually easy to do…)
you realize that this is a significant change from your previous statement about calculating the difference between the two times
yes, now you do need the rows for the earliest and latest times, if you are doing additional calulations besides just the difference between the two times
Thanks for helping out. from the fms1 table I need the info from the fields:
odometer (double), total_fuel (float), engine_hours (float), created(datetime) and ofcourse the gps_unit_id (int(10))
for the first 4 fields I want to calculate the difference within a given time period.
In the first post I posted my sollution for a single unit:
(
SELECT `fms1`.`gps_unit_id` , `odometer` , `total_fuel` , `engine_hours` , `fms1`.`created`
FROM fms1
WHERE `fms1`.`gps_unit_id` =76
AND `fms1`.`created` >= '2012-03-26 00:00:00'
ORDER BY `fms1`.`created` ASC
LIMIT 1
)
UNION (
SELECT `fms1`.`gps_unit_id` , `odometer` , `total_fuel` , `engine_hours` , `fms1`.`created`
FROM fms1
WHERE `fms1`.`gps_unit_id` =76
AND `fms1`.`created` <= 'NOW'
ORDER BY `fms1`.`created` DESC
LIMIT 1
)
with PHP code I would calculate the different values between the odometer, total_fluel, engine_hours and created (the time between the two records (which will be smaller then the time difference between the two bounds of the timeperiod))
I hope this makes sense now. The problem I have is that I have no idea how to do this for multiple units (all the units coupled to a single user) and make the most of mySQL power of selecting only data I need. that is:
I dont want all the records in a time period, just the first and last in a timeperiod (see example above)
Do some or all calculation by mySQL if this is efficient.
In the first post said I tried this with a LEFT JOIN, GROUP BY and ORDER BY, but I couldn’t make it work. and that was even without letting mysql doing the calculation.
SELECT latest.gps_unit_id
, latest.odometer - earliest.odometer AS diff_odometer
, latest.total_fuel - earliest.total_fuel AS diff_total_fuel
, latest.engine_hours - earliest.engine_hours AS diff_engine_hours
, TIMEDIFF(latest.created - earliest.created) AS diff_created
FROM ( SELECT gps_unit_id
, MIN(created) AS min_created
, MAX(created) AS max_created
FROM fms1
WHERE created BETWEEN '2012-03-26' AND NOW()
GROUP
BY gps_unit_id ) AS mm
INNER
JOIN fms1 AS earliest
ON earliest.gps_unit_id = mm.gps_unit_id
AND earliest.created = mm.min_created
INNER
JOIN fms1 AS latest
ON latest.gps_unit_id = mm.gps_unit_id
AND latest.created = mm.max_created
Hi, sorry for not getting back to you urlier. Moving house took a lott of time out of my sceduele.
It does looks elegant to me, but I don’t think my mySQL skills are good enough to fully understand this one…
I’m not sure this does what I want it to do. I get results like this:
I changed the dates to 2012-04-02 and NOW to get the results for this week.
also timediff() works with two commaseperated values.
You should know, there are currently 7 units submitting data into the fms1 table; 76, 79, 80, 81, 90, 91 and 92
4 belong to one user, and 3 to another.
If I just run the inside select:
SELECT gps_unit_id, MIN( created ) AS min_created, MAX( created ) AS max_created
FROM fms1
WHERE created
BETWEEN '2012-04-02'
AND NOW( )
GROUP BY gps_unit_id
note the difference in times for the min_created and max_created fields.
for unit 76 the dates should be: 2012-04-02 04:14:19 and 2012-04-05 20:15:20
found by sorting (SELECT *FROM fms1WHERE createdBETWEEN ‘2012-04-02’ AND NOW( ) AND gps_unit_id =76 ORDER BY fms1.created DESC LIMIT 0 , 30 ) ASC and DESC
We can select the units beloging to one user with a inner join
INNER JOIN gps_units ON gps_units.id = fms1.gps_unit_id
So, each user has units
each unit submits data to the fms1 table.
I like for each unit belonging to a user the difference between first and last record within a time period. I think you got that last part right, but somehow selecting or grouping the right units is not working as it should.
Please let me know what kind of info you need to fix this.
