Oracle中将DD-MM-YY格式日期转换为epoch时间戳并新增列的咨询

前置说明 #

你给出的示例03-06-21转换为1622671200，对应的是东八区（UTC+8）2021年6月3日零点的Unix时间戳，以下方案默认适配该时区，其他时区可调整时差参数。 #

操作步骤 #

1. 新增Unix时间戳存储列 #

执行SQL新增数值类型列，10位长度足够存储秒级Unix时间戳：

ALTER TABLE 你的表名 ADD (unix_timestamp NUMBER(10));

2. 验证转换逻辑（必须先验证再执行更新） #

先运行查询语句确认转换结果符合预期，避免直接更新出错：

SELECT
  原日期列名,
  (TO_DATE(原日期列名, 'DD-MM-RR') - TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 86400 - 28800 AS computed_unix_ts
FROM 你的表名
WHERE ROWNUM <= 10;

说明：

• 用DD-MM-RR而不是DD-MM-YY格式，是因为RR格式会自动识别两位数年份所属的世纪，避免21年被识别为1921的问题
• 86400是一天的秒数，28800是东八区和UTC的时差秒数，其他时区可自行调整该值
• 如果原日期列本身是Oracle DATE类型而非字符串存储，直接去掉TO_DATE(原日期列名, 'DD-MM-RR')的转换，直接用原列名即可

3. 批量更新全表数据 #

验证转换结果正确后，执行更新语句写入新列：

UPDATE 你的表名
SET unix_timestamp = (TO_DATE(原日期列名, 'DD-MM-RR') - TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 86400 - 28800;
COMMIT;

可选配置：自动同步新插入数据 #

如果需要后续新插入的数据自动生成时间戳，可创建触发器：

CREATE OR REPLACE TRIGGER trg_表名_auto_unix_ts
BEFORE INSERT ON 你的表名
FOR EACH ROW
BEGIN
  :NEW.unix_timestamp := (TO_DATE(:NEW.原日期列名, 'DD-MM-RR') - TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 86400 - 28800;
END;
/

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