Graphing Time series in MySQL

We'll generate a series of sensor recordings from raw sensor readings that can come in at arbitrary times:

Begin by creating a table of recordings:

  CREATE TABLE sensor_readings (
    id int not null auto_increment,
    name varchar(255) not null,
    value int not null,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY (`name`)
  );


  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor1', 1, '2023-02-10 20:00:00');
  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor2', 5, '2023-02-11 20:00:00');
  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor2', 10, '2023-02-11 20:00:00');

  -- Wait some time for next recording

  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor1', 3, '2023-02-16 20:00:00');
  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor2', 7, '2023-02-17 20:00:00');


  -- Wait some time for next recording

  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor1', 15, '2023-02-22 20:00:00');
  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor2', 18, '2023-02-22 20:00:00');

mysql> select * from sensor_readings;
+----+---------+-------+---------------------+
| id | name    | value | created_at          |
+----+---------+-------+---------------------+
|  1 | sensor1 |     1 | 2023-02-10 20:00:00 |
|  2 | sensor2 |     5 | 2023-02-11 20:00:00 |
|  3 | sensor2 |    10 | 2023-02-11 20:00:00 |
|  4 | sensor1 |     3 | 2023-02-16 20:00:00 |
|  5 | sensor2 |     7 | 2023-02-17 20:00:00 |
|  6 | sensor1 |    15 | 2023-02-22 20:00:00 |
|  7 | sensor2 |    18 | 2023-02-22 20:00:00 |
+----+---------+-------+---------------------+
7 rows in set (0.00 sec)

Now let's graph the data over a specific range:

WITH RECURSIVE date_range as (
      SELECT '2023-01-30' AS start_date, '2023-01-31' as end_date 

        UNION ALL 

      SELECT DATE_ADD(start_date, INTERVAL 1 DAY), DATE_ADD(end_date, INTERVAL 1 DAY) FROM date_range 
      WHERE start_date < '2023-02-25'

    ), distinct_sensors as (

        SELECT date_range.start_date, date_range.end_date, name FROM date_range 
        CROSS JOIN (SELECT DISTINCT(name) as name FROM sensor_readings) T

    ), output as (

        SELECT start_date, end_date, distinct_sensors.name, value, 
               ROW_NUMBER() OVER(PARTITION BY start_date, name) as row_num FROM distinct_sensors
        LEFT JOIN sensor_readings ON 
             sensor_readings.created_at <= distinct_sensors.end_date
         AND sensor_readings.created_at > distinct_sensors.start_date
         AND sensor_readings.name = distinct_sensors.name

    )

    -- SELECT * FROM date_range
    -- SELECT * FROM distinct_sensors
    -- SELECT * FROM output

SELECT start_date, name, IFNULL(value, 0) as value 
FROM output WHERE row_num = 1

;

+------------+---------+-------+
| start_date | name    | value |
+------------+---------+-------+
| 2023-01-30 | sensor1 |     0 |
| 2023-01-30 | sensor2 |     0 |
| 2023-01-31 | sensor1 |     0 |
| 2023-01-31 | sensor2 |     0 |
| 2023-02-01 | sensor1 |     0 |
| 2023-02-01 | sensor2 |     0 |
| 2023-02-02 | sensor1 |     0 |
| 2023-02-02 | sensor2 |     0 |
| 2023-02-03 | sensor1 |     0 |
| 2023-02-03 | sensor2 |     0 |
| 2023-02-04 | sensor1 |     0 |
| 2023-02-04 | sensor2 |     0 |
| 2023-02-05 | sensor1 |     0 |
| 2023-02-05 | sensor2 |     0 |
| 2023-02-06 | sensor1 |     0 |
| 2023-02-06 | sensor2 |     0 |
| 2023-02-07 | sensor1 |     0 |
| 2023-02-07 | sensor2 |     0 |
| 2023-02-08 | sensor1 |     0 |
| 2023-02-08 | sensor2 |     0 |
| 2023-02-09 | sensor1 |     0 |
| 2023-02-09 | sensor2 |     0 |
| 2023-02-10 | sensor1 |     1 |
| 2023-02-10 | sensor2 |     0 |
| 2023-02-11 | sensor1 |     0 |
| 2023-02-11 | sensor2 |     5 |
| 2023-02-12 | sensor1 |     0 |
| 2023-02-12 | sensor2 |     0 |
| 2023-02-13 | sensor1 |     0 |
| 2023-02-13 | sensor2 |     0 |
| 2023-02-14 | sensor1 |     0 |
| 2023-02-14 | sensor2 |     0 |
| 2023-02-15 | sensor1 |     0 |
| 2023-02-15 | sensor2 |     0 |
| 2023-02-16 | sensor1 |     3 |
| 2023-02-16 | sensor2 |     0 |
| 2023-02-17 | sensor1 |     0 |
| 2023-02-17 | sensor2 |     7 |
| 2023-02-18 | sensor1 |     0 |
| 2023-02-18 | sensor2 |     0 |
| 2023-02-19 | sensor1 |     0 |
| 2023-02-19 | sensor2 |     0 |
| 2023-02-20 | sensor1 |     0 |
| 2023-02-20 | sensor2 |     0 |
| 2023-02-21 | sensor1 |     0 |
| 2023-02-21 | sensor2 |     0 |
| 2023-02-22 | sensor1 |    15 |
| 2023-02-22 | sensor2 |    18 |
| 2023-02-23 | sensor1 |     0 |
| 2023-02-23 | sensor2 |     0 |
| 2023-02-24 | sensor1 |     0 |
| 2023-02-24 | sensor2 |     0 |
| 2023-02-25 | sensor1 |     0 |
| 2023-02-25 | sensor2 |     0 |
+------------+---------+-------+
54 rows in set (0.00 sec)

This is problematic if you have tons of sensors and find that data doesn't change often. In these cases, you may find you store excessive amounts of data, in which case you'll have to query the existing data and then decide to store the result if the prior value is different than the new value.

SELECT created_at, name, value FROM sensor_readings WHERE name = 'sensor1' ORDER BY created_at DESC LIMIT 1;
INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor1', 1, '2023-02-10 20:00:00')

You could also create a secondary table that ledgers the time series with only updated rows:

  TRUNCATE TABLE sensor_readings;
  ALTER TABLE sensor_readings ADD UNIQUE KEY (`name`);

  CREATE TABLE sensor_readings_ledger (
    id int not null auto_increment,
    name varchar(255) not null,
    value int not null,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY (`name`)
  );

DELIMITER $$
CREATE TRIGGER ledger_sensor_readings_inserts
AFTER INSERT ON `sensor_readings`
FOR EACH ROW
BEGIN
  INSERT INTO sensor_readings_ledger (name, value, created_at) VALUES (NEW.name, NEW.value, NEW.created_at);
END;$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER ledger_sensor_readings_updates
AFTER UPDATE ON `sensor_readings`
FOR EACH ROW
BEGIN
  IF (OLD.value != NEW.value) THEN
      INSERT INTO sensor_readings_ledger (name, value, created_at) VALUES (NEW.name, NEW.value, NEW.created_at);
  END IF; 
END;$$
DELIMITER ;


  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor1', 1, '2023-02-10 20:00:00') ON DUPLICATE KEY UPDATE value = 1, created_at = '2023-02-10 20:00:00';
  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor2', 5, '2023-02-11 20:00:00') ON DUPLICATE KEY UPDATE value = 5, created_at = '2023-02-11 20:00:00';
  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor2', 10, '2023-02-11 20:00:00') ON DUPLICATE KEY UPDATE value = 10, created_at = '2023-02-11 20:00:00';

  -- Wait some time for next recording

  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor1', 3, '2023-02-16 20:00:00') ON DUPLICATE KEY UPDATE value = 3, created_at = '2023-02-16 20:00:00';
  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor2', 7, '2023-02-17 20:00:00') ON DUPLICATE KEY UPDATE value = 7, created_at = '2023-02-17 20:00:00';


  -- Wait some time for next recording

  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor1', 15, '2023-02-22 20:00:00') ON DUPLICATE KEY UPDATE value = 15, created_at = '2023-02-22 20:00:00';
  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor2', 18, '2023-02-22 20:00:00') ON DUPLICATE KEY UPDATE value = 18, created_at = '2023-02-22 20:00:00';

  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor1', 15, '2023-02-25 20:00:00') ON DUPLICATE KEY UPDATE value = 15, created_at = '2023-02-25 20:00:00';
  INSERT INTO sensor_readings (name, value, created_at) VALUES ('sensor2', 18, '2023-02-25 20:00:00') ON DUPLICATE KEY UPDATE value = 18, created_at = '2023-02-25 20:00:00';


    mysql> select * from sensor_readings;
    +----+---------+-------+---------------------+
    | id | name    | value | created_at          |
    +----+---------+-------+---------------------+
    |  1 | sensor1 |    15 | 2023-02-22 20:00:00 |
    |  2 | sensor2 |    18 | 2023-02-22 20:00:00 |
    +----+---------+-------+---------------------+
    2 rows in set (0.01 sec)

    mysql> select * from sensor_readings_ledger;
    +----+---------+-------+---------------------+
    | id | name    | value | created_at          |
    +----+---------+-------+---------------------+
    |  1 | sensor1 |     1 | 2023-02-10 20:00:00 |
    |  2 | sensor2 |     5 | 2023-02-11 20:00:00 |
    |  3 | sensor2 |    10 | 2023-02-11 20:00:00 |
    |  4 | sensor1 |     3 | 2023-02-16 20:00:00 |
    |  5 | sensor2 |     7 | 2023-02-17 20:00:00 |
    |  6 | sensor1 |    15 | 2023-02-22 20:00:00 |
    |  7 | sensor2 |    18 | 2023-02-22 20:00:00 |
    +----+---------+-------+---------------------+
    7 rows in set (0.00 sec)

Anil's Blog

Anil's Blog

Graphing Time series in MySQL