CODEWITHSUNDEEP
postgresqltimestampincrement
Stefan
Stefan

Reputation: 1543

How to update column with an increasing timestamp

I have a table with about 6000 entries and I want add a column with a timestamp for each of them. Beginning with a start value (could be NOW()) the update should increase the first value by one hour per entry.

'2014-12-04 01:00:00.000'
'2014-12-04 02:00:00.000'
'2014-12-04 03:00:00.000'

I was thinking about a window function to do this.

The table has a unique id and a geometry index (gid).

Upvotes: 2

Views: 2927

Answers (1)

user330315
user330315

Reputation:

Something like this:

update the_table
  set ts_column = current_timestamp + interval '1' hour * x.rn
from (
  select id, row_number() over (order by id) rn
  from the_table
) x
where x.id = the_table.id;

current_timestamp returns the current time at the start of the transaction, so the value will not change during the runtime of the statement and thus the interval is always added to the same base value.

Upvotes: 5

Related Questions