How do I make this procedure generate random times (HH:MM:SS) but with the current date?-CodePudding

I need this procedure to generate 10M random timestamps but it has to use the current date each time it is called, how do i do this? I know that there's a CURRENT_TIMESTAMP() function, but I'm not sure how to implement it.

DELIMITER $$
CREATE PROCEDURE producer() 
    

    BEGIN
        DECLARE a INT DEFAULT 0;
        WHILE a < 10000000 DO
            INSERT INTO CaseLog (`TIMESTAMP_IN`) 
            VALUES (FROM_UNIXTIME(UNIX_TIMESTAMP('2021-09-22 00:00:00') FLOOR(RAND()*86399))
            );
            SET a = a 1;
        END WHILE;
    END$$
DELIMITER ;

CodePudding user response：

Use CURRENT_DATE() to get today's date, and use that instead of the hard-coded date.

DELIMITER $$
CREATE PROCEDURE producer() 

    BEGIN
        DECLARE today_timestamp INT;
        SET today_timestamp = UNIX_TIMESTAMP(CURRENT_DATE());
        DECLARE a INT DEFAULT 0;
        WHILE a < 10000000 DO
            INSERT INTO CaseLog (`TIMESTAMP_IN`) 
            VALUES (FROM_UNIXTIME(today_timestamp FLOOR(RAND()*86400)));
            SET a = a 1;
        END WHILE;
    END$$
DELIMITER ;

Also, you should multiply RAND() by 86400, not 86399. The result of RAND() is always less than 1.0, so you don't have to worry that it will return 86400.