I work on a system that records fuel consumption. We collect fuel measurement samples every 10 seconds, it measures the fuel flow in l/h. These measurements end up in a hypertable like this:
Table "public.fuel_measurement" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-----------+----------+--------- time | timestamp with time zone | | not null | tag_id | integer | | not null | liter_per_hour | double precision | | not null | fuel_type_id | integer | | not null | 2```
fuel_type_id is a foreign key to a table that keeps track of the physical properties of different types of fuel (most notably the density of the fuel). The engines our system monitors are capable of switching between different types of fuel to run on.
Now I need to be able to work out the total mass of fuel for a given period. For this I first need calculate the volume from the average flow. Then using the density of the fuel this can be converted to mass.
But I can’t quite work out how to do this when multiple types of fuel have been used during the selected period.
I came up with this query:
SELECT tag_id, density, (avg(liter_per_hour) / 3600) * EXTRACT(EPOCH FROM (max(time) - min(time))) AS liters FROM fuel_measurement fm JOIN fuel_type ft ON (fm.fuel_type_id = ft.fuel_type_id) WHERE tag_id IN (13, 26) AND time >= '2022-07-01T00:00:00.000Z' AND time < '2022-08-01T00:00:00.000Z' GROUP BY tag_id, density;
This gives me volume of fuel for each engine and the density for each type of fuel. That’s is easy to convert back to mass in the application code.
But this will not yield correct results when the engine started out with fuel A ran fuel B for a while and then switched back to fuel A during the queried period of time.
I simply can not work out how to deal with this. Any ideas?