如何实现SQL查询中Unix时间戳到DateTime格式的转换

核心逻辑是保留created_at字段的原始毫秒级Unix时间戳格式（避免索引失效），通过数据库内置时间函数，把用户可读的yyyy-MM-dd HH:mm:ss格式时间字符串转换为和字段存储格式一致的时间戳做匹配。终端用户只需要修改引号内的时间值即可，不需要理解Unix时间戳概念。

以下是不同主流数据库的对应改写方式：

• MySQL

  select * from my_tabel 
  where created_at >= UNIX_TIMESTAMP('2022-06-18 10:00:00') * 1000;
  

  说明：UNIX_TIMESTAMP会将标准格式的时间字符串转为秒级Unix时间戳，乘以1000后和字段存储的毫秒级时间戳精度对齐。如果使用非标准时间格式，可以搭配STR_TO_DATE自定义解析规则，例如UNIX_TIMESTAMP(STR_TO_DATE('2022-06-18 10:00:00', '%Y-%m-%d %H:%i:%s')) * 1000。

• PostgreSQL

  select * from my_tabel 
  where created_at >= EXTRACT(EPOCH FROM TIMESTAMP '2022-06-18 10:00:00')::bigint * 1000;
  

  说明：如果需要指定时区避免时间偏移，可以加上时区配置，例如EXTRACT(EPOCH FROM TIMESTAMP '2022-06-18 10:00:00' AT TIME ZONE 'Asia/Shanghai')::bigint * 1000。

• SQL Server

  select * from my_tabel 
  where created_at >= DATEDIFF_BIG(ms, '1970-01-01 00:00:00', '2022-06-18 10:00:00');
  

  说明：必须使用DATEDIFF_BIG而非普通DATEDIFF，避免毫秒级时间戳数值过大出现整数溢出问题。

注意事项 #

1. 不要反向将created_at字段转为时间字符串再做比较，类似WHERE FROM_UNIXTIME(created_at/1000) >= '2022-06-18 10:00:00'的写法会导致字段上的索引完全失效，数据量较大时查询速度会急剧下降，同时容易触发时区配置导致的时间偏差问题。
2. 改写前先确认字段存储的时间戳精度：如果是10位的秒级时间戳，去掉SQL语句中*1000的逻辑即可，避免精度不匹配导致查询结果错误。
3. 交付给终端用户时，可以在时间字符串上方加一行注释-- 请修改此处查询时间，格式为 年-月-日 时:分:秒，进一步降低用户的使用门槛。

内容的提问来源于stack exchange，提问作者Hojjat Shaygani