从SQL Server迁移至PostgreSQL：十六进制转timestamp问题求助

首先，我们得先理清几个关键点，再解决你的报错和转换问题：

为什么你会遇到这些错误？ #

• 错误1（0x语法报错）：PostgreSQL中十六进制字面量的前缀是\x，而不是SQL Server用的0x。直接写CAST(0x00009DD500000000 AS timestamp)会让PostgreSQL无法识别语法，自然报错。
• 错误2（列不存在）：去掉0之后，x00009DD500000000会被PostgreSQL当成列名来解析，而你的表中显然没有这个列，所以提示列不存在。

另外要注意：SQL Server里的timestamp类型其实是行版本标识（现在官方叫rowversion），是8字节的二进制值，并非真正的日期时间类型；而PostgreSQL的timestamp是标准的日期时间类型，两者本质不同，所以不能直接通过CAST转换。

PostgreSQL中如何实现转换？ #

PostgreSQL没有内置函数直接完成这个转换，但我们可以通过解析SQL Server datetime二进制的存储规则来手动实现。SQL Server的datetime类型存储规则是：

• 前4字节：从1900-01-01开始的天数（小端字节序）
• 后4字节：当天从午夜开始的毫秒数（小端字节序）

方法1：一次性SQL查询转换 #

如果你只是转换单个值，可以用以下查询：

WITH raw_data AS (
  -- 把SQL Server的0x开头十六进制字符串去掉前缀，转成bytea
  SELECT decode(substring('0x00009DD500000000' FROM 3), 'hex') AS bin_value
),
parsed_values AS (
  SELECT
    -- 解析日期部分：小端转整数，得到1900-01-01以来的天数
    get_byte(bin_value, 3) << 24 | get_byte(bin_value, 2) << 16 | get_byte(bin_value, 1) << 8 | get_byte(bin_value, 0) AS days_offset,
    -- 解析时间部分：小端转整数，得到当天的毫秒数
    get_byte(bin_value, 7) << 24 | get_byte(bin_value, 6) << 16 | get_byte(bin_value, 5) << 8 | get_byte(bin_value, 4) AS ms_offset
  FROM raw_data
)
SELECT
  ('1900-01-01'::DATE + days_offset * INTERVAL '1 day') + ms_offset * INTERVAL '1 millisecond' AS postgresql_timestamp
FROM parsed_values;

方法2：封装成可复用函数 #

如果需要批量转换，建议创建一个自定义函数，方便重复调用：

CREATE OR REPLACE FUNCTION sqlserver_datetime_bin_to_pg_timestamp(bin_data BYTEA)
RETURNS TIMESTAMP AS $$
DECLARE
  days_since_1900 INTEGER;
  ms_since_midnight INTEGER;
BEGIN
  -- 校验输入必须是8字节（SQL Server datetime的标准长度）
  IF LENGTH(bin_data) != 8 THEN
    RAISE EXCEPTION '输入的二进制数据必须是8字节长度（符合SQL Server datetime存储格式）';
  END IF;

  -- 解析日期部分（小端字节序转整数）
  days_since_1900 := get_byte(bin_data, 3) << 24 | get_byte(bin_data, 2) << 16 | get_byte(bin_data, 1) << 8 | get_byte(bin_data, 0);
  -- 解析时间部分（小端字节序转整数）
  ms_since_midnight := get_byte(bin_data, 7) << 24 | get_byte(bin_data, 6) << 16 | get_byte(bin_data, 5) << 8 | get_byte(bin_data, 4);

  -- 计算最终的timestamp
  RETURN ('1900-01-01'::DATE + days_since_1900 * INTERVAL '1 day') + ms_since_midnight * INTERVAL '1 millisecond';
END;
$$ LANGUAGE plpgsql;

使用这个函数时，只需要把十六进制字符串转成bytea传入即可：

-- 直接转换十六进制字符串
SELECT sqlserver_datetime_bin_to_pg_timestamp(decode('00009DD500000000', 'hex'));

-- 如果原始字符串是0x开头的，先去掉前缀
SELECT sqlserver_datetime_bin_to_pg_timestamp(decode(substring('0x00009DD500000000' FROM 3), 'hex'));

验证结果 #

针对你提供的0x00009DD500000000，转换后得到的timestamp是2007-10-01 00:00:00，和SQL Server中CAST(0x00009DD500000000 AS datetime)的结果一致。

内容的提问来源于stack exchange，提问作者Juan Luis de Reiset