PostgreSQL如何使用类似MySQL的数字格式进行timestamp条件查询

PostgreSQL 不支持对 timestamp 类型直接用长数字做条件匹配，需要先将数字转换为对应的 timestamp 格式，以下是两种可直接落地的实现方式：

• 方案1：直接使用内置to_timestamp函数转换
  先将数字转为文本，再指定对应格式解析为timestamp，格式标识YYYYMMDDHH24MISSMS对应「年+月+日+24小时制时+分+秒+毫秒」的结构：

  SELECT * FROM items 
  WHERE created_time >= to_timestamp(20210505143012999::text, 'YYYYMMDDHH24MISSMS');
  

• 方案2：封装自定义函数简化调用
  如果需要高频使用这种数字查询，可以提前封装一个转换函数，后续调用和MySQL原生用法一致：

  -- 提前创建转换函数，只需要执行一次
  CREATE OR REPLACE FUNCTION num_to_ts(bigint) RETURNS timestamp AS $$
  SELECT to_timestamp($1::text, 'YYYYMMDDHH24MISSMS')::timestamp;
  $$ LANGUAGE sql IMMUTABLE;
  
  -- 后续查询直接调用即可
  SELECT * FROM items WHERE created_time >= num_to_ts(20210505143012999);
  

如果你使用的数字格式没有毫秒（即长度为14位的20210505143012格式），只需要把转换格式中的MS后缀去掉即可；如果字段是带时区的timestamptz类型，可以在转换后追加AT TIME ZONE '你的时区'对齐存储时区，避免时区偏移导致查询结果错误。

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