I have a procedure that contains code like this:
processStart := current_timestamp;
-- run statement(s)
processEnd := current_timestamp;
elapsed := processEnd - processStart;
raise notice 'My Statement, elapsed time: %', elapsed;
The idea is, I want to get the amount of time it takes for a statement or collection of statements to run.
The problem is, this returns 00:00:00
for subsecond elapsed times. I really want to see the milliseconds. How can I do this?
There's questions and answers about using EXTRACT
and EPOCH
, but this seems to be at the "second" level, and that is not granular enough for my purposes.
UPDATE
Using @twn08's answer, I ultimately arrived at the following solution:
I declared the following variables:
declare
processStart timestamp;
elapsed numeric(18,3);
processFinish timestamp;
then, prior to starting the process:
processStart := clock_timestamp();
after the process was finished, 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;
this worked swimmingly.
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 in documentation the following example:
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
Result: 982384720.12
Before 9.0 there is:
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');
Result: 982384720
Based on this it is not entirely clear whether my example will work prior to version 9.0