Skip to main content
added 314 characters in body
Source Link
user1822
user1822

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') ;

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()

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') ;
Source Link
user1822
user1822

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()