基于单调递增id列更新updated_at字段生成递增历史时间序列

核心思路 #

利用id列的单调递增特性，基于一个过去的基准时间，给每条记录添加与id正相关的时间偏移量，即可保证updated_at始终单调递增且全部处于过去时间范围内。

方案一：固定间隔递增（简单易控） #

选择一个足够早的基准时间，按id添加固定时长的偏移，确保最终时间都在过去：

UPDATE temp1
SET updated_at = '2023-01-01 00:00:00'::timestamp + (id * INTERVAL '5 minutes');

也可以基于当前时间反向计算，确保所有时间都落在过去：

UPDATE temp1
SET updated_at = CURRENT_TIMESTAMP - '365 days'::interval + (id * INTERVAL '1 hour');

方案二：随机间隔递增（贴近真实场景） #

如果需要非固定间隔的递增时间戳，可通过累加随机时长的方式，保证每条记录的时间都比前一条晚：

UPDATE temp1 t_target
SET updated_at = (
    SELECT '2023-01-01'::timestamp + SUM(random() * INTERVAL '2 hours')
    FROM temp1 t_source
    WHERE t_source.id <= t_target.id
);

验证结果 #

执行更新后，查询表数据确认效果：

SELECT * FROM temp1;

示例输出：

id | bar  |         updated_at         
----+------+----------------------------
  1 | val1 | 2023-01-01 00:47:12.345678
  2 | val2 | 2023-01-01 02:15:03.123456
  3 | val3 | 2023-01-01 03:59:45.987654

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