How to measure time difference in milliseconds in a function?

Question

I would like to calculate the difference between the starting time (variable of type timestamptz) and the NOW() moment when my function finishes.

select * from _get_date_diff_ms(NOW(), NOW())

Using supabase.io with PG 13.3, I get this error message:

operator does not exist: timestamp with time zone - double precision"

CREATE OR REPLACE FUNCTION _get_date_diff_ms(p_begin_time timestamptz, p_end_time timestamptz)
RETURNS int AS
$$
    BEGIN
        RETURN ROUND ((
            EXTRACT (EPOCH FROM p_begin_time -
            EXTRACT (EPOCH FROM p_end_time)
        ) * 1000));
    END;
$$ LANGUAGE plpgsql;

Does `ROUND(EXTRACT(EPOCH FROM p_end_time - p_begin_time) *1000)` work? Remember: now() will always have the same value in a transaction: the start time of it. See also [EXTRACT, date_part](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT) — Steeeve, Nov 05 '21 at 22:53
You are right. I am getting the start and end time inside the SAME function (p/lpgsql), I tried clock_timestamp() and now() and they give this DOUBLE PRECISION error — jfbaro, Nov 05 '21 at 23:46
What's the idiomatic way of calculating the time spent by the PL/PGSQL function (in milliseconds)? From the BEGIN (start_time) until the RETURN (end_time)? I want to store this value into a column in another TABLE called (search_history). Thanks — jfbaro, Nov 05 '21 at 23:53

Adrian Klaver · Answer 1 · 2021-11-05T23:58:50.157

I think what you are looking for is :



CREATE OR REPLACE FUNCTION public._get_date_diff_ms(p_begin_time timestamp with time zone)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
    BEGIN
        RETURN round((EXTRACT(EPOCH FROM clock_timestamp() - p_begin_time ) * 1000));
    END;                                                                                
$function$
;

 select _get_date_diff_ms(now() - interval '.5 sec');
 _get_date_diff_ms 
-------------------
              500

I threw a negative interval in the function call to actually get a diff. For what the the various time functions do see Current time.

score 2 · Accepted Answer · answered Nov 06 '21 at 03:52

operator does not exist: timestamp with time zone - double precision

That's just because of wrong parentheses. You meant to write:

        RETURN ROUND ((
            EXTRACT (EPOCH FROM p_begin_time) -    -- !
            EXTRACT (EPOCH FROM p_end_time)
        ) * 1000);

But the whole approach is broken. It's been pointed out by now that now() is a stable value within a Postgres transaction. See:

Difference between now() and current_timestamp

Moreover, for your expressed purpose, it makes no sense to pass a "starting time" to begin with.

Either you mean the starting time of the transaction: then use now() anywhere in the function.
Or you mean the starting time of the function: then use clock_timestamp() at the start of the function.

Assuming the latter, I suggest:

CREATE OR REPLACE FUNCTION public._get_date_diff_ms() -- no parameter
 RETURNS numeric  -- !
 LANGUAGE plpgsql AS
$func$
DECLARE
   _start_ts timestamptz := clock_timestamp();  -- !
BEGIN
   PERFORM pg_sleep(1);   --  do something here (1 sec example)
   RETURN round(1000 * EXTRACT(epoch FROM clock_timestamp() - _start_ts), 3);
END                                                                                
$func$;

Call:

test=> SELECT public._get_date_diff_ms();
 _get_date_diff_ms 
-------------------
          1003.440

I return numeric with 3 fractional digits instead of integer to capture microseconds. Else, it would just report 0 for many fast operations.

See:

Get execution time of PostgreSQL query

How to measure time difference in milliseconds in a function?

2 Answers2