Postgres - Multiple attempts to get current time return the same time value
I was trying to profile a store procedure that builds a temporary table before returning it to the caller. Multiple inserts, updates and transformations are performed on the temporary table so I wanted to see which were taking the longest to complete; to determine this, I decided to output the duration of each of these operations by recording the start and stop time.
For some reason the start and stop time were always the same; I was expecting to see at least one millisecond of difference. It turns out that the method I was using to get the time CURRENT_TIMESTAMP is transaction based, meaning it will return the time when the stored procedure is executed.
If you need to get the time from the point the statement is executed within a stored procedure, you need to use a statement based method to get the time such as clock_timestamp().