Update a timestamp column to make a sequence of increasing timestamps along a sorted numeric column

Question

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)

Timur Shtatland · Accepted Answer · 2023-04-06T18:52:41.837

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:

Update a timestamp column to make a sequence of increasing timestamps along a sorted numeric column

1 Answers1