Mysql 将时间戳四舍五入到最接近的半小时而不遗漏丢失的数据

Situation:个

想象一下这样一个脚本，它在完成时将当前时间戳和其他一些数据插入到MySQL表中.它每30分钟执行一次，有时根本不执行，这会在数据中留下空白.

Goal:个

有一个查询，当没有数据时，获取时间戳四舍五入为最接近的半小时的所有数据和空行(除时间戳之外的所有字段都应为空).

Restrictions:个

表 struct 、数据本身和脚本都不能更改.

Problem:个

我能想出的唯一能产生预期结果的解决方案是不能扩展的.目前，实际的表约有50‘000行，完成查询已经花费了15分钟以上.

Example:个

CREATE TABLE IF NOT EXISTS `statuses` (
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `status` INT NOT NULL DEFAULT '0',
    PRIMARY KEY (`timestamp`)
);

INSERT
    IGNORE INTO `statuses` (`timestamp`, `status`)
VALUES
    ('2023-01-01 00:03:34', '164850'),
    ('2023-01-01 00:31:23', '794088'),
    ('2023-01-01 03:31:28', '686754'),
    ('2023-01-01 04:01:15', '684711'),
    ('2023-01-01 05:31:35', '116777'),
    ('2023-01-01 06:01:52', '469332'),
    ('2023-01-01 06:31:55', '816300'),
    ('2023-01-01 08:33:53', '309583'),
    ('2023-01-01 09:03:54', '847976'),
    ('2023-01-01 09:31:33', '812517');

WITH RECURSIVE `timestamps`(`timestamp`) AS (
    SELECT
        (
            SELECT
                FROM_UNIXTIME(
                    UNIX_TIMESTAMP(MIN(`timestamp`)) - MOD(UNIX_TIMESTAMP(MIN(`timestamp`)), 1800)
                )
            FROM
                `statuses`
        )
    UNION
    ALL
    SELECT
        DATE_ADD(`timestamp`, INTERVAL 30 MINUTE)
    FROM
        `timestamps`
    WHERE
        `timestamp` < (
            SELECT
                FROM_UNIXTIME(
                    UNIX_TIMESTAMP(MAX(`timestamp`)) - MOD(UNIX_TIMESTAMP(MAX(`timestamp`)), 1800)
                )
            FROM
                `statuses`
        )
)
SELECT
    `t`.`timestamp`,
    `s`.`status`
FROM
    `timestamps` AS `t`
    LEFT OUTER JOIN `statuses` AS `s` ON `t`.`timestamp` = FROM_UNIXTIME(
        UNIX_TIMESTAMP(`s`.`timestamp`) - MOD(UNIX_TIMESTAMP(`s`.`timestamp`), 1800)
    )
ORDER BY
    `t`.`timestamp` ASC;