Question 334710 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to add new timestamp column and populate it times incrementing by 1 second

I have a table that has about 60 million rows that I use for testing. I want to add a column test_time that should be unique across all rows and spaced 1 second apart from each other.

So for instance:

Row 1: 2024-01-01 00:00:00
Row 2: 2024-01-01 00:00:01
Row 3: 2024-01-01 00:00:02
Row 4: 2024-01-01 00:00:03
...

Is there an easy way to accomplish this through SQL or do I need to write a script to connect to the db and manually do this?

Tags:

2

Answers


  1. 0

    You can use generate sequence function,

    SELECT * FROM generate_series('2024-01-01 00:00:00'::timestamp,
                              '2024-01-01 12:00:00', '1 second');

    enter image description here

    For your requirement, use row_number with your actual table and join it with the above query and generate the date with second difference

    Login or Signup to reply.
  2. 0

    You can achieve this using SQL directly, utilizing the ROW_NUMBER() function along with a timestamp generation mechanism. The idea is to use the ROW_NUMBER() to generate incremental numbers and then add these numbers to a base timestamp to get your desired result. Here’s an example query:

    -- Assuming you have a table named 'your_table' and you want to add a column 'test_time'
ALTER TABLE your_table
ADD COLUMN test_time TIMESTAMP;

-- Update the 'test_time' column with incremental timestamps
UPDATE your_table
SET test_time = '2024-01-01'::TIMESTAMP + (ROW_NUMBER() OVER ()) * INTERVAL '1 second';

    This query adds a new column test_time to your existing table and updates it with timestamps incrementing by 1 second for each row. Adjust the table and column names according to your database schema.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search