How to get execution time in postgres

Question

I need, in a plpgsql script, to get the execution time of the last query in a variable for several purposes (calculation, display and storage), so the psql \timing option is not what I look for because I can't manipulate the result time. Do you know if there is anything like the "get diagnostics" command, but for execution time (or work around) ?:

get diagnostics my_var := EXECUTION_TIME;

I couldn't find anything else than row_count and result_oid...

Answer 1

You can compare clock_timestamp() before and after the query:

do $$
declare t timestamptz := clock_timestamp();
begin
 perform pg_sleep(random());
 raise notice 'time spent=%', clock_timestamp() - t;
end
$$ language plpgsql;

Sample result:

NOTICE: time spent=00:00:00.59173