Is QuestDB suitable for querying interval data, such as energy meters

user_18100484 0
Viewed 112

I want to design a database to store IoT data from several utility meters (electricity, gas, water) using mqtt protocol. Is QuestDB suitable for this type of data where it would store meter readings (it is the difference between readings that is mainly of interest as opposed to the readings themselves)? More specifically, I am asking if the database would allow me to quickly and easily query the following? If so, some example queries would be helpful.

  • calculate energy consumption of a specific/all meters in a given date period (essentially taking the difference between readings between two dates for example)
  • calculate the rate of energy consumption with time over a specific period for trending purposes

Also, could it cope with situations where a faulty meter is replaced and therefore the reading is reset to 0 for example, but consumption queries should not be affected?

My initial ideas:

  • Create a table with Timestamp, MeterID, UtilityType, MeterReading, ConsumptionSinceLastReading fields
  • When entering a new meter reading record, it would calculate consumption since last reading and store it in the relevant table field. Although this doesn't seem like the right approach and perhaps a time series db like QuestDB has a built-in solution for this kind of problem?
questdb time-series intervals database
1 Answers

I think you have approached the problem the right way. By storing both the actual reading and the difference to the previous reading you have everything you need. You could calculate the energy consumption and the rate with SQL statements.

Consumption for a period:

select sum(ConsumptionSinceLastReading) from Readings
where ts>'2022-01-15T00:00:00.000000Z' and ts<'2022-01-19T00:00:00.000000Z';

Rate (consumption/sec) within a period:

select (max(MeterReading)-min(MeterReading)) / (cast(max(ts)-min(ts) as long)/1000000.0) from (
    select MeterReading, ts from Readings
    where ts>'2022-01-15T00:00:00.000000Z' and ts<'2022-01-20T00:00:00.000000Z' limit 1
        union
    select MeterReading, ts from Readings
    where ts>'2022-01-15T00:00:00.000000Z' and ts<'2022-01-20T00:00:00.000000Z' limit -1
);

There is no specific built-in support for your use case in QuestDB but it would do a good job. The above selects are optimised since they use the LIMIT keyword.

user_183554850
Related