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?

  • 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. – HPage Apr 27 '17 at 7:21
  • 1
    Can you sacrifice detailed data for a daily record updated with min, max and running average? – A.Rashad Aug 25 '17 at 11:17

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|||||

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|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.