英文:
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 | NULL2 | val2 | NULL3 | 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 | NULL2 | val2 | NULL3 | val3 | NULL(3 rows)
答案1
得分: 0
以下是已翻译的内容：
一种简单的实现方法是使用日期/时间数学运算符。例如，这会生成一系列时间戳，其起始日期为过去3个月，每一行的时间戳相对于起始日期增加 id 秒：
update temp1set 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.6449662 | val2 | 2023-01-06 17:50:00.6449663 | 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 temp1set 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.6449662 | val2 | 2023-01-06 17:50:00.6449663 | val3 | 2023-01-06 17:50:01.644966(3 rows)
See also:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论