-3

I have a database that keeps a temperature readouts, which are being added - let's say - every minute:

+----+---------------------+-------------+-----------+
| id |      timestamp      | temperature | sensor_id |
+----+---------------------+-------------+-----------+
|  1 | 2021-12-14 10:10:00 |        68.2 |         1 |
|  2 | 2021-12-14 10:11:00 |        69.0 |         1 |
|  3 | 2021-12-14 10:12:00 |        68.2 |         1 |
+----+---------------------+-------------+-----------+

What will be the most efficient way to get the temperature for hourly / daily / weekly temperature line charts?

For example if I want to display a temperature chart for a day, I want to put 24 points on the chart:

temperature line chart

If we are saying about hourly chart I assume it would be acceptable to calculate an average on the fly when getting the data, but what about monthly chart?

What comes to my mind is to keep the average values in separate tables for days/weeks/months and calculate those using some scheduled job. Or is there some other way to acheive that?

Improve this question
3
  • 9
    Have you benchmarked how long it takes to get a monthly average? I suspect you may be worrying about a problem that doesn't exist.
    – Philip Kendall
    Dec 14, 2021 at 20:48
  • Of course, that should be considered before looking at a time series database! Don't optimize before you know you need to do it.
    – Hans-Martin Mosner
    Dec 14, 2021 at 21:11
  • 1
    Questions asking for the "most efficient" way without any context or measurings are always nonsensical, because efficiency is a term different people define differently. Most efficient in terms of CPU cycles, memory resource consumption, or in programming hours, for example. Note any kind of performance optimization has to be seen in relationship to your budget, current knowledge, and existing infrastructure, time frame and performance requirements, all things we don't know, since you did not mention them.
    – Doc Brown
    Dec 16, 2021 at 13:38

1 Answer 1

Reset to default
4

You're not the first one having this requirement, and there are a number of solutions. If you haven't heard the term time series database it's now time to put it into your favorite search engine and read a bit.

These databases are made exactly for this purpose. You can find open source implementations for a wide range of use cases.

Unless you're somehow locked into your relational database and can't use one of these alternatives, there's almost no reason to reinvent the wheel.

Improve this answer

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