Insert into value as generate row with different minutes

Question

I try backup and restore using WAL-E/WAL-G.

I can backup and restore.

But i want check recovery_target_time.

During recovery, by default WAL-E will attempt to pull and restore all WAL segments available. If you wish to restore to a specific POINT IN TIME than specify.

# restore to 4:38PM on 3/6/2012
recovery_target_time = '2012-03-06 16:38:00'

I think need generate value every minute long time and Insert into table.

Found insert events with CURRENT_TIMESTAMP

INSERT INTO events (ts,description) VALUES (CURRENT_TIMESTAMP,'disc full');

Found insert SELECT NOW()

INSERT INTO indexing_table(created_at) SELECT NOW() FROM generate_series(1,100);

This is no answer :(

How build table?

         created_at           
-------------------------------
2019-02-13 14:16:20.799219+03
2019-02-13 14:17:20.799219+03
2019-02-13 14:18:20.799219+03
2019-02-13 14:19:20.799219+03

or

    created_at           
-------------------
2019-02-13 14:16:20
2019-02-13 14:17:20
2019-02-13 14:18:20
2019-02-13 14:19:20

Need generate row with different minutes.

Any help would be appreciated in creating these tables! Thanks!

Answer 1

I am not entirely sure what your question is, but if you are confused that all rows have the same timestamp value, this is because now() and current_timestamp return the time at the start of the transaction.

Quote from the manual

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp

And then:

clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.

So if I understood your question correctly, you probably want to use clock_timestamp() rather than now()

---

Another option is to use generate_series() to generate timestamps:

INSERT INTO indexing_table(created_at) 
SELECT *
FROM generate_series(timestamp '2019-01-01 00:00:00', 
                     timestamp '2019-03-31 00:00:00', 
                     interval '1 minute') ;