Get interval in milliseconds

Question

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.

You can extract milliseconds: `SELECT EXTRACT(MILLISECONDS FROM Now());` — Tomas Greif, May 30 '13 at 13:33

Tomas Greif · Accepted Answer · 2013-05-30T13:46:02.067

23

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

edited May 30 '13 at 13:46

answered May 30 '13 at 13:37

Tomas Greif

21,685
23
106
155

While this answer was not very clear, it did help me figure out what I needed to do. See my edited question for the final result. – Jeremy Holovacs May 30 '13 at 15:14
FYI: `EXTRACT(EPOCH FROM t)` definitely works in 8.4. – Asclepius Dec 03 '13 at 00:35

liquidki · Answer 2 · 2015-05-22T19:17:53.463

Here's a one-liner to calculate the elapsed time in milliseconds from a 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 a primary and standby PostgreSQL server, so simply replace pg_last_xact_replay_timestamp() with your TIMESTAMP.

To see what it's doing a bit 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)

score 4 · Answer 3 · answered May 10 '18 at 08:33

Based on @TomasGreif's answer, I made a little datediff function in case you need to do millisecond datediffs often:

create function datediff_ms(timestamptz, timestamptz) returns INTEGER as
$$
    begin
        return (EXTRACT('epoch' from $2) - EXTRACT('epoch' from $1)) * 1000;
    end;
$$ LANGUAGE plpgsql;

Usage:

select datediff_ms(time1, time2)
from sometables;

score 2 · Answer 4 · answered Nov 10 '22 at 22:05

2

Note that you can extract the epoch from the interval, which might make the syntax a bit more concise.

select extract('epoch' from (t2-t1))

versus

select extract('epoch' from t2) - extract('epoch' from t1)

answered Nov 10 '22 at 22:05

Anssssss

3,087
31
40

Get interval in milliseconds

4 Answers4