CODEWITHSUNDEEP
databasetime-seriesintervalsquestdb
elsami
elsami

Reputation: 21

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

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.

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:

Upvotes: 1

Views: 238

Answers (1)

glasstiger
glasstiger

Reputation: 71

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.

Upvotes: 1

Related Questions