3

I have an application that measures that continuously. (About every second - it depends on the device) For example sake, let's say that data is CPUTemp (as used as an example in an answer to a related question: Clean Architecure: Creating an entity from a set of other entities)

Each measurement's data is written to the CPU_TEMP_SAMPLE table.

At the end of each day I compress the data calculating the min, max and average for that day and writing the data to a CPU_TEMP_DAY table.

I have a CpuTempRepository that get's the data either by calculating it from the CPU_TEMP_SAMPLE table (if it is for the current day) or just reading it from the CPU_TEMP_DAY table if it is for a previous day.

I want to be able to get the min and max reading for specific intervals from the CPU_TEMP_SAMPLE. (So instead of just returning the max temperature sample for that day I want to get the max temperature recorded over 30 seconds - so I am actually compressing 30 seconds worth of measurements into one sample)

How would I do this? One way is to write each interval's min, max and average data to a new table CPU_TEMP_INTERVAL every 30 seconds and then get the max & min from there, but I am not sure if this is the right way - what if I need to calculate for different intervals?

Improve this question
2
  • In general I struggle to work with data across time intervals. (Generating reports etc..) I am never sure on how to store and access the data. If somebody could point me in the direction of good resources to learn this I would appreciate it. Commented Apr 27, 2017 at 7:21
  • 1
    Can you sacrifice detailed data for a daily record updated with min, max and running average? Commented Aug 25, 2017 at 11:17

2 Answers 2

Reset to default
1

Executing multiple queries in short period of time and then repeat the process would stress your database and would consume a lot more of resources.

You can send the data to an queue tag it with timestamp

timestamp : measurement

then have a worker that process the queue an executes queries to the database.

You can also temporarily work with in memory cache and have it create a Set every hour that holds measurements taken every 30 seconds , send the serialized data to be stored into database.

There are other approaches thought but consider that you don't have to store to database every in small intervals although I doubt that it will be a problem nowdays with database able to perform well under stress.

Improve this answer
0

You don't say what type of system you're working on. If your temperature is in the 0-255° range, it can be stored as a single byte per sample. So storing an entire days worth of temperature samples at 1 sample per second = 1 byte * 60 samples/minute * 60 minutes/hour * 24 hours / day = 86,400 bytes/day, or about 86k. This is peanuts on most modern systems, including cell phones. On smaller embedded systems, it might be a bigger deal. But if you can spare ~90k, just store a table locally for the temperatures and do the calculations on-the-fly when requested.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.