mysql 时间段统计

-- 统计某个月份的订单数量
SELECT COUNT(*) AS order_count, MONTH(order_date) AS month
FROM orders
WHERE YEAR(order_date) = 2023 AND MONTH(order_date) = 4
GROUP BY MONTH(order_date);

-- 统计每小时的订单数量
SELECT COUNT(*) AS order_count, HOUR(order_date) AS hour
FROM orders
WHERE order_date >= '2023-04-01' AND order_date < '2023-05-01'
GROUP BY HOUR(order_date);

-- 统计某个时间段的订单总金额
SELECT SUM(amount) AS total_amount
FROM orders
WHERE order_date >= '2023-04-01' AND order_date < '2023-05-01';

-- 创建索引
CREATE INDEX idx_order_date ON orders(order_date);

-- 分页查询示例
SELECT COUNT(*) AS order_count, MONTH(order_date) AS month
FROM orders
WHERE YEAR(order_date) = 2023 AND MONTH(order_date) = 4
GROUP BY MONTH(order_date)
LIMIT 10 OFFSET 0;

-- 统一时间格式示例
UPDATE orders
SET order_date = STR_TO_DATE(order_date, '%Y-%m-%d %H:%i:%s');

-- 使用函数转换示例
SELECT COUNT(*) AS order_count, MONTH(STR_TO_DATE(order_date, '%Y-%m-%d')) AS month
FROM orders
WHERE YEAR(STR_TO_DATE(order_date, '%Y-%m-%d')) = 2023 AND MONTH(STR_TO_DATE(order_date, '%Y-%m-%d')) = 4
GROUP BY MONTH(STR_TO_DATE(order_date, '%Y-%m-%d'));