英文:
Update a timestamp column to make a sequence of increasing timestamps along a sorted numeric column
问题
我有一个具有单调递增整数列（id）的表格。我需要更新updated_at列（时间戳），以创建过去的时间戳递增序列。确切的步长不重要，只要时间戳单调递增。
create table temp1 (
id serial not null,
bar varchar(35),
updated_at timestamp
);
insert into temp1
(bar)
values
('val1'),
('val2'),
('val3')
;
select * from temp1;
id | bar | updated_at
----+------+------------
1 | val1 | NULL
2 | val2 | NULL
3 | val3 | NULL
(3 rows)
英文:
I have a table with column of monotonically increasing integers (id). I need to update column updated_at (timestamp) to create an increasing series of timestamps, all in the past. The exact step does not matter, as long as the timestamps monotonically increase.
create table temp1 (
id serial not null,
bar varchar(35),
updated_at timestamp
)
;
insert into temp1
(bar)
values
('val1'),
('val2'),
('val3')
;
select * from temp1;
id | bar | updated_at
----+------+------------
1 | val1 | NULL
2 | val2 | NULL
3 | val3 | NULL
(3 rows)
答案1
得分: 0
以下是已翻译的内容：
一种简单的实现方法是使用日期/时间数学运算符。例如，这会生成一系列时间戳，其起始日期为过去3个月，每一行的时间戳相对于起始日期增加 id 秒：
update temp1
set updated_at = (now() - interval '3 months' + interval '1 second' * id)
;
select * from temp1;
id | bar | updated_at
----+------+----------------------------
1 | val1 | 2023-01-06 17:49:59.644966
2 | val2 | 2023-01-06 17:50:00.644966
3 | val3 | 2023-01-06 17:50:01.644966
(3 rows)
参见：
- PostgreSQL: Documentation: 15: 9.9. 日期/时间函数和运算符
- https://stackoverflow.com/q/11391085/967621
- https://stackoverflow.com/q/14113469/967621
英文:
An easy way to accomplish this would be to use the date/time math operators. For example, this generates a sequence of timestamps with a start date = 3 months in the past, and incrementing the timestamp in each row by id seconds compared with the start date:
update temp1
set updated_at = (now() - interval '3 months' + interval '1 second' * id)
;
select * from temp1;
id | bar | updated_at
----+------+----------------------------
1 | val1 | 2023-01-06 17:49:59.644966
2 | val2 | 2023-01-06 17:50:00.644966
3 | val3 | 2023-01-06 17:50:01.644966
(3 rows)
See also:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论