mysql 生成时间序列

CREATE TABLE time_series (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE
);

DELIMITER $$
CREATE PROCEDURE generate_time_series()
BEGIN
    DECLARE start_date DATE := '2023-01-01';
    DECLARE end_date DATE := '2023-12-31';
    DECLARE current_date DATE;

    WHILE start_date <= end_date DO
        INSERT INTO time_series (date) VALUES (start_date);
        SET start_date = DATE_ADD(start_date, INTERVAL 1 DAY);
    END WHILE;
END$$
DELIMITER ;

CALL generate_time_series();

import mysql.connector
from datetime import datetime, timedelta

# 连接到MySQL数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)
cursor = db.cursor()

# 创建表
cursor.execute("""
CREATE TABLE IF NOT EXISTS time_series (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE
)
""")

# 生成时间序列并插入数据
start_date = datetime(2023, 1, 1)
end_date = datetime(2023, 12, 31)

current_date = start_date
while current_date <= end_date:
    cursor.execute("INSERT INTO time_series (date) VALUES (%s)", (current_date.date(),))
    current_date += timedelta(days=1)

# 提交更改并关闭连接
db.commit()
cursor.close()
db.close()

INSERT INTO time_series (date) VALUES
('2023-01-01'), ('2023-01-02'), ..., ('2023-12-31');

SELECT date FROM time_series WHERE date NOT IN (
    SELECT DATE_ADD('2023-01-01', INTERVAL n DAY)
    FROM (
        SELECT @row := @row + 1 AS n
        FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t,
        (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
        (SELECT @row := 0) r
    ) numbers
    WHERE n <= DATEDIFF('2023-12-31', '2023-01-01')
);