mysql 自定义时间段

SELECT *
FROM orders
WHERE created_at BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59';

SELECT *
FROM orders
WHERE CONVERT_TZ(created_at, 'UTC', 'Asia/Shanghai') BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59';

-- 创建索引
CREATE INDEX idx_created_at ON orders(created_at);

-- 分区表示例
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    created_at DATETIME,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);