时间戳

首页 > 解决方案 > 创建触发器以获取时间戳之间的每小时差异

postgresql - 创建触发器以获取时间戳之间的每小时差异

问题描述

我有一个表，我想在插入一行后计算两列之间的时间差（以小时为单位）。每当对表执行插入或更新时，我想设置一个触发器来执行此操作。

我的专栏是 delay_start、delay_stop 和 delay_duration。我想做以下事情：

delay_duration = delay_stop - delay_start

结果应该是数字 (4,2) 值并进入 delay_duration 类别。以下是我到目前为止的内容，但由于某种原因它不会填充该列。

BEGIN
INSERT INTO public.deckdelays(delay_duration)
VALUES(DATEDIFF(hh, delay_stop, delay_start));
RETURN NEW;
END;

我对这一切都很陌生，所以如果有人能提供帮助，我将不胜感激！

标签： postgresql

解决方案

如果您有 Postgres 12 或更高版本，您可以将 delay_duration 定义为生成的列。这使您可以消除触发器。

create table deckdelays(id           integer generated always as identity
                       , delay_start timestamp   
                       , delay_stop  timestamp
                       , delay_duration numeric(4,2)
                              generated always as 
                              ( extract(epoch from (delay_stop - delay_start))/3600 )
                              stored
                        --, other attributes
                       );

在此处查看演示。

但如果你坚持触发：

create or replace 
function delayduration_func()
 returns trigger 
language plpgsql 
as $$
begin
    new.delay_duration = (extract(epoch from (deckdelays.delay_stop - deckdelays.delay_start))/3600)::numeric;
return new;
end;
$$;

create trigger delaydurationset1
before insert 
    or update of delay_stop, delay_start 
    on deckdelays
execute procedure delayduration_func();

变化：

  • 在触发之前而不是之后。前触发器可以修改列中的值而无需额外的 DML 语句，后触发器不能。在同一个表的触发器内对表发出 DML 语句可能会导致所有类型的问题。如果可能，尽量避免。
  • 触发器名和函数名不一样。可能只是我，但我不喜欢具有相同名称的不同事物。虽然它的工作原理经常会导致混乱。尽可能避免混淆。
  • 触发器在 delay_start 更新时触发。delay_start 或 delay_end 的更新也会更新 delay_duration。

推荐阅读