mysql database, 2400 records for 1500 rows I need to create a new database, recording data for around 1500 sensors.

I need to store hourly data, and keep a rolling database for the past 100 days.

My first thought was to create a database with one row for each sensor (so around 1500 rows), and one column per hour - so 2400 columns.

Reading this other SO question (Too many columns in MySQL), this is clearly a bad idea!!

So how can I best organise and set up my table, to store 2400 data points for each of 1500 sensors, while creating a manageable table that is efficient?
Rolling 100 days -- I recommend using PARTITIONing and break it into weekly partitions.  It will be about 17 partitions.  Details:  http://mysql.rjweb.org/doc.php/partitionmaint .  DROP PARTITION is much faster than DELETE.

It is possible to map between "hour" and "datetime" (or "timestamp") with some arithmetic involving FLOOR( ... / 3600).  Then that will fit nicely in MEDIUMINT UNSIGNED and replace 3 of Vérace's columns.

Don't have an AUTO_INCREMENT if you have a 'natural' PK.  Suggest

PRIMARY KEY(sensor_id, hour)


and

PARTITION BY (hour)


You may, but probably won't, need

INDEX(hour, ...)