Database for temperature logging

I would like to do datalogging, let’s start with the simple temperature value:
Log the temperature value of each room every minute.
All data older than 1 week is condensed to min and max value of the hour.
All data older than 1 year is condensed to min and max of the day.

Hardware is a raspberry pi. Will this be good or do I need extra storage?
I’m familiar with postgreSQL. What database would you suggest to use?
What mechanism would you use for starting the condensation algorithm, cron job?

For numerical data rrd4j persistence would fit exactly your needs.


it depends on your PI-setup.

Data are stored on your default storage device - and normal flash devices have a limited amounts of allowed writes per flash block.
So it is a good idea to use an extra USB-Stick for your cyclic writes. This makes it easier to replace it if it breaks.

As stated by @Syn rrd4j seems to fit, however storing the max AND min for the condensed timeframes needs a tweaking.
I would use two custom setups of rrd4j., the first would condense to the max value and the second to the min value. Using the documentation as linked by @Syn you should be able to create the needed setup with 3 archives. You have to specify for how long you want to keep stored data, rrd4j has to have a fixed size ( that migth be a spoiler for you).
Since the rrd4j database is fixed in its size in can easily be handled on the raspi running openhab (I do it that way).

Thank you, round robin database was the keyword I was missing.
How to deal with “duration”, use case:
Temperature + duration the heater is ON.

If you read the documentation you’ll find the archive setup. Each archive has settings for steps and rows. Steps are the number of consecutive readings that are used the create a single entry in this archive. Rows are the number of such Steps in that archive.

Maybe I misunderstood your last question. Above answer is toward: how to define the duration that rrd4j stores data.
If you question was more toward to: “How to log how long the heater is on or off.” That migth be aproblem since rrd4j is storing numericals only.

@opus: Yes, I did mean “How to log how long the heater is on or off.”. Same situation is for counting, “How many times did I switch the light per hour”.
So I’ll need some preprocessing for counting/frquency and duration data to get an analogue value.

All I need to store a single numeric: counts or duration (make an item for this). Make a rule: Reset at start of every intervall to 0. Do count+1 on a trigger or duration+1 every cycle if ‘ON’. At the end of the intervall that value is saved in the round robin DB. Sounds easy…

You seem to have a plan!
Make up your mind what data to store.
Heater: I’d accumulate the time the heater is on for each day and store that in the database. For condensation I’d sum weeks and keep values covering a year (maybe two years).
Light: I’d count the ON-switching per day and store that in the database. For condensation I’d sum weeks and keep values covering a year (maybe two years).

Note that the database would need to store a value every minute in order to work (rrd4j requirement)., although the to be saved value would change once a day only.

Rethinking the above, the suggested policy would NOT allow to use the built-in consolidation function ( sum up for a week) because you’d would have 1440 values values keeping the day value. Sorry for that. Thinking…


var Number log_duration_light_WoZi1 = 0
var Number log_duration_light_WoZi2 = 0

rule “Reset”
Time cron “0 0 * * * ?”
//Den alten Wert in die DB schreiben
//Die Zahlenwerte initialisieren
log_duration_light_WoZi1 = 0
log_duration_light_WoZi2 = 0

rule “Duration”
//Jede Minute, 5 Sekunden versetzt zu reset
Time cron “5 * * * * ?”
//Wenn an, dann 1 hochzählen
if (Helligkeit_WoZi1.state > 0) {
log_duration_light_WoZi1 = log_duration_light_WoZi1 +1
if (Helligkeit_WoZi2.state > 0) {
log_duration_light_WoZi2 = log_duration_light_WoZi2 +1
logInfo(“Duration”, "WoZi1 = " +log_duration_light_WoZi1.toString )
logInfo(“Duration”, "WoZi2 = " +log_duration_light_WoZi2.toString )

I would use items instead of global variables for the durations since items will be persisted. Note that you need to " .postUpdate" them.
I’m sorry but the use of consolation functions does not support what you are trying to do with older data ( which changed from your original post!). Storing the increasing value over a day would work and you can consolidate to a single value for 24 houres using the max function, however an additional archive for a longer timeframe would be created from the original minute-wise stored values. Neither of the consolidation functions ( max, mix, first, last, average or total) would create a meaningful value for that timeframe.
I’m out of ideas. I would just store the 24 hour values.

I couldn’t get my head into rrd4j. The documentation was terrible with their “boxes”.

I once had a discussion about doing this on industrial scale. Those guys where using Cassandra database for the task. Looking around in the nosql world, I also found apache kafka and piplineDB based on postgresql promissing candidates.

What about publishing the data on MQTT and consume it with one of those DB solutions?

Sorry, but that is above my knowledge.