postgresql 以毫秒为单位获取间隔
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16837280/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get interval in milliseconds
提问by Jeremy Holovacs
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:00for subsecond elapsed times. I really want to see the milliseconds. How can I do this?
问题是，这会返回00:00:00亚秒经过的时间。我真的很想看到毫秒。我怎样才能做到这一点？
There's questions and answers about using EXTRACTand EPOCH, but this seems to be at the "second" level, and that is not granular enough for my purposes.
有关于使用EXTRACTand的问题和答案EPOCH，但这似乎处于“第二”级别，这对于我的目的来说不够细化。
UPDATE
更新
Using @twn08's answer, I ultimately arrived at the following solution:
使用@twn08 的回答，我最终得出了以下解决方案：
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.
这很有效。
回答by Tomas Greif
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 documentationthe following example:
对于 9.0+ 版本，您可以在文档中阅读以下示例：
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
Result: 982384720.12
Before 9.0 there is:
在 9.0 之前有：
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
基于此，尚不清楚我的示例是否可以在 9.0 版之前运行
回答by liquidki
Here's a one-liner to calculate the elapsed time in milliseconds from a past TIMESTAMP to now():
这是一个单行代码，用于计算从过去的 TIMESTAMP 到 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.
上面的示例计算了主 PostgreSQL 服务器和备用 PostgreSQL 服务器之间的复制延迟，因此只需替换pg_last_xact_replay_timestamp()为您的 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)
回答by Shaul Behr
Based on @TomasGreif's answer, I made a little datediff function in case you need to do millisecond datediffs often:
根据@TomasGreif 的回答，我做了一个小 datediff 函数，以防您需要经常执行毫秒 datediffs：
create function datediff_ms(timestamptz, timestamptz) returns INTEGER as
$$
begin
return (EXTRACT('epoch' from ) - EXTRACT('epoch' from )) * 1000;
end;
$$ LANGUAGE plpgsql;
Usage:
用法：
select datediff_ms(time1, time2)
from sometables;
