CODEWITHSUNDEEP
mysqlsqlraspberry-pi
KinsDotNet
KinsDotNet

Reputation: 1560

What is best practice for storing a measurement, tracked constantly, over time?

I'm just thinking about the database design of an application that I am going to create soon. It will use a temperature sensor to track the environmental temperature and display a graph of the temperature over time.

I'm just realizing that I've never done something like this before. In terms of the database, do you just create a row for every measurement you take? That is, to have a table, and insert a new row containing the temperature and the current datetime, at a given interval?

Upvotes: 0

Views: 76

Answers (2)

James K. Lowden
James K. Lowden

Reputation: 7837

insert a new row containing the temperature and the current datetime, at a given interval?

Yup. Two columns, value and time. Use the datetime datatype native to your DBMS, so you can operate on it to get statistics over intervals or at particular times, such as at noon every Monday or whatever.

There's nothing new or unusual about capturing time-varying information. That very phrase appears in Codd's paper introducing the relational model, in 1970.

Upvotes: 0

Enrico Dias
Enrico Dias

Reputation: 1487

Assuming that you will use a numeric field for the temperature, I don't see any problem in adding a row for each measurement. Even if you measure every second, it will be just 3600 rows per hour. MySql can easily deal with million or even billion rows.

Upvotes: 1

Related Questions