generate MySQL demo database with millions of rows with time stamp in 3 minute.
//First formula
//months to second 6 Month = 15552000 sec
//Series interval = 3 sec
//Suppose today date is ‘Saturday, January 23, 2021 12:34:40 PM GMT+05:30’
//Epoch time will be 1611385480 google Epoch time to know more..
//six months back Epoh time will be 1611385480 - 15552000 (6 months time in seconds) = 1595833480
//First create table
CREATE TABLE demoplant ( id INT PRIMARY KEY AUTO_INCREMENT, tim TIMESTAMP DEFAULT CURRENT_TIMESTAMP, machine1 INT, machine2 INT, machine3 INT, machine4 INT, machine5 INT, machine6 INT);
// For create procedure
DELIMITER //
CREATE PROCEDURE filldemodata (IN NumRows INT, IN Unix_epo INT, IN interval_sec INT, IN MinVal1 INT, IN MaxVal1 INT, IN MinVal2 INT, IN MaxVal2 INT, IN MinVal3 INT, IN MaxVal3 INT, IN MinVal4 INT, IN MaxVal4 INT, IN MinVal5 INT, IN MaxVal5 INT, IN MinVal6 INT, IN MaxVal6 INT)
BEGIN
DECLARE i INT;
DECLARE J INT;
DECLARE k INT;
SET i = 1;
SET j = Unix_epo;
SET k = interval_sec;
START TRANSACTION;
WHILE i = NumRows DO
INSERT INTO demoplant (tim, machine1, machine2, machine3, machine4, machine5, machine6) VALUES (from_unixtime (j, '%Y-%m-%d %H:%i:%s'), MinVal1 + CEIL(RAND() * (MaxVal1 - MinVal1)), MinVal2 + CEIL(RAND() * (MaxVal2 - MinVal2)), MinVal3 + CEIL(RAND() * (MaxVal3 - MinVal3)), MinVal4 + CEIL(RAND() * (MaxVal4 - MinVal4)), MinVal5 + CEIL(RAND() * (MaxVal5 - MinVal5)), MinVal6 + CEIL(RAND() * (MaxVal1 - MinVal6)) );
SET i = i + 1;
SET j = j + k;
END WHILE;
COMMIT;
END//
DELIMITER ;
//CALLING PROCEDURE
//CALL filldemodata (number of rows, 6months back epoch time, series interval in seconds, machine1 min value, machine1 max value, machine2 min value, machine2 max value, machine3 min value, //machine3 max value, machine4 min value, machine4 max value, machine5 min value, machine5 max value, machine6 min value, machine6 max value);
//Example
Call filldemodata ( 5200000, 1595833480, 3, 100, 105, 200, 210, 120, 127, 160, 168, 180, 186, 250, 260);