PostgreSQL 11中将含空值长数字文本及UNIX时间戳转为日期的方法

处理这类转换的核心是先搞定空值和无效值的校验，避免转换函数直接报错，然后分两步完成：文本转数值、数值转日期。还要注意你的长数字是秒级还是毫秒级UNIX时间戳——这决定了最终的转换逻辑：

• 如果是秒级UNIX时间戳（比如1549324800）：

  SELECT 
    CASE 
      WHEN your_text_column IS NOT NULL AND your_text_column ~ '^[0-9]+$' -- 校验是否为纯数字
      THEN TO_CHAR(TO_TIMESTAMP(your_text_column::BIGINT), 'DD/MM/YYYY')::DATE
      ELSE NULL -- 空值或无效值返回空
    END AS converted_date
  FROM your_table;
  

• 如果是毫秒级UNIX时间戳（比如问题2里的1549324800000）：
  PostgreSQL的TO_TIMESTAMP()默认只认秒级时间戳，所以得先除以1000转换单位：

  SELECT 
    COALESCE(
      DATE(TO_TIMESTAMP((your_text_column::BIGINT)/1000)),
      NULL
    ) AS converted_date
  FROM your_table;
  

  如果字段里可能混有非数字的无效值，还是建议用CASE WHEN加正则校验的方式，比COALESCE更稳妥。

你的思路方向没问题，但HubSpot返回的这个时间戳是毫秒级的，直接用TO_TIMESTAMP()会解析出完全错误的年份（因为它把毫秒当成秒处理了）。结合PostgreSQL 11的特性，给你两个方案：

简洁版（适合确定字段全是有效数字的场景） #

如果能保证properties__vape_station__value里全是13位的纯数字时间戳，直接这样写最简洁：

SELECT 
  DATE(TO_TIMESTAMP((properties__vape_station__value::BIGINT)/1000)) AS converted_date
FROM your_table;

先把文本转成BIGINT，除以1000转成秒级时间戳，再用TO_TIMESTAMP()转成时间类型，最后用DATE()直接提取日期部分——比TO_CHAR()再转DATE更高效。

健壮版（处理空值、无效值） #

如果字段里可能有空值或者非数字的垃圾数据，一定要加校验避免查询报错：

SELECT 
  CASE 
    WHEN properties__vape_station__value IS NOT NULL 
         AND properties__vape_station__value ~ '^[0-9]{13}$' -- 校验是否是13位毫秒级时间戳
    THEN DATE(TO_TIMESTAMP((properties__vape_station__value::BIGINT)/1000))
    ELSE NULL
  END AS converted_date
FROM your_table;

这里用正则^[0-9]{13}$确保输入是13位纯数字（符合毫秒级UNIX时间戳的长度），空值和无效值直接返回NULL，不会中断查询。

补充：PostgreSQL 12+有TO_TIMESTAMP_MICRO()可以直接处理微秒级时间戳，但11版本里还是用除以1000的方法最稳妥，兼容性拉满。

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