Get interval in milliseconds

Question

I have a procedure containing this code:

processStart := current_timestamp; -- run statement(s) processEnd := current_timestamp; elapsed := processEnd - processStart; raise notice 'My Statement, elapsed time: %', elapsed; 

The idea is that I want to get the time it takes to execute a statement or collection of statements.

The problem is that it returns 00:00:00 for subsecond elapsed times. I really want to see milliseconds. How can i do this?

There are questions and answers about using EXTRACT and EPOCH , but it seems like a "second" level, and this is not enough for my purposes.

UPDATE

Using the @ twn08 answer, I ended up with the following solution:

I declared the following variables:

 declare processStart timestamp; elapsed numeric(18,3); processFinish timestamp; 

then before starting the process:

 processStart := clock_timestamp(); 

After completing the process, I ran this:

 processFinish := clock_timestamp(); elapsed := cast(extract(epoch from (processFinish - processStart)) as numeric(18,3)); raise notice 'My Statement, elapsed time: % ms', elapsed; 

it worked smoothly.

Answer 1

 with t as (select Now() as tend, Now() - interval '10 seconds 552 milliseconds' as tstart ) select extract('epoch' from tend) - extract('epoch' from tstart) from t 

Note:

For version 9.0+, you can read the documentation in the following example:

 SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08'); Result: 982384720.12 

Until 9.0 there are :

 SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08'); Result: 982384720 

Based on this, it is not clear whether my example will work before version 9.0

Answer 2

Here is one layer to calculate elapsed time in milliseconds from the past TIMESTAMP to now ():

 SELECT ROUND((EXTRACT (EPOCH FROM now()) - EXTRACT (EPOCH FROM pg_last_xact_replay_timestamp())) * 1000) AS "replication_lag (ms)"; 

The above example calculates the replication lag between the primary and backup PostgreSQL server, so just replace pg_last_xact_replay_timestamp() with your TIMESTAMP.

To find out what he is doing a little more clearly:

 SELECT ROUND (( EXTRACT (EPOCH FROM now()) - EXTRACT (EPOCH FROM pg_last_xact_replay_timestamp()) ) * 1000) AS "replication_lag (ms)"; 

Output:

  replication_lag (ms) ---------------------- 562 (1 row)