悠悠楠杉
网站页面
MySQL时间戳转日期及范围查询终极指南
09/01
一、时间戳与日期格式互转
1. UNIX时间戳转标准日期
sql
SELECT
FROM_UNIXTIME(1633046400) AS basic_convert,
FROM_UNIXTIME(1633046400, '%Y-%m-%d %H:%i:%s') AS formatted_date;
输出结果：2021-10-01 00:00:00（两种格式相同）
2. 日期转UNIX时间戳
sql
SELECT
UNIX_TIMESTAMP('2023-05-15 14:30:00') AS timestamp_sec,
UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) * 1000 AS timestamp_ms;
注意：毫秒级时间戳需要乘以1000
3. 时区转换技巧
sql
SET time_zone = '+8:00';
SELECT
FROM_UNIXTIME(1633046400) AS beijing_time,
CONVERT_TZ(FROM_UNIXTIME(1633046400), '+08:00', '+00:00') AS london_time;
二、时间范围查询7种方案
1. 基础BETWEEN用法
sql
SELECT * FROM orders
WHERE order_time BETWEEN '2023-01-01' AND '2023-01-31 23:59:59';
2. 时间戳范围查询（推荐）
sql
SELECT * FROM user_logs
WHERE log_time >= UNIX_TIMESTAMP('2023-06-01')
AND log_time < UNIX_TIMESTAMP('2023-06-02');
3. 动态时间范围
sql
-- 查询最近30天数据
SELECT * FROM sales
WHERE sale_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
4. 日期函数截取比较
sql
SELECT * FROM events
WHERE DATE(event_time) = '2023-03-15';
5. 带时区的范围查询
sql
SELECT * FROM international_orders
WHERE CONVERT_TZ(create_time, '+00:00', '+08:00') BETWEEN '2023-08-01 08:00:00' AND '2023-08-01 18:00:00';
6. 按小时段筛选
sql
SELECT * FROM website_visits
WHERE HOUR(visit_time) BETWEEN 9 AND 17;
7. 跨年查询优化方案
sql
SELECT * FROM annual_reports
WHERE YEAR(report_date) = 2023
AND MONTH(report_date) BETWEEN 1 AND 6;
三、性能优化关键点
索引失效陷阱：避免在WHERE条件中对字段使用函数sql
-- 错误写法（导致索引失效）
SELECT * FROM table WHERE DATE(create_time) = '2023-01-01';-- 正确写法
SELECT * FROM table
WHERE createtime >= '2023-01-01' AND createtime < '2023-01-02';存储引擎差异：
- MyISAM：支持全文索引的时间查询
- InnoDB：推荐使用复合索引 (date_column, id)
分区表策略：按RANGE分区提升大表查询性能
sql CREATE TABLE sensor_data ( id INT, record_time DATETIME, value FLOAT ) PARTITION BY RANGE (TO_DAYS(record_time)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')), PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')) );
四、特殊场景处理
1. 毫秒级时间处理
sql
SELECT
UNIX_TIMESTAMP('2023-01-01 12:34:56.789') * 1000 +
MICROSECOND('2023-01-01 12:34:56.789')/1000 AS precise_timestamp;
2. 闰秒问题应对
sql
-- 使用DATETIME(6)存储微秒级时间
ALTER TABLE critical_events MODIFY COLUMN event_time DATETIME(6);
3. 历史数据迁移方案
sql
-- 将字符串日期转为时间戳批量更新
UPDATE archive_data
SET timestamp_column = UNIX_TIMESTAMP(STR_TO_DATE(date_string, '%m/%d/%Y %H:%i:%s'))
WHERE id BETWEEN 10000 AND 20000;
