在我的表中,有两个字段fail_count和fail_count_timestamp。fail_count字段存储登录失败尝试的计数,fail_count_timestamp将存储失败尝试的最近时间戳。所以我想在时间戳超过60分钟后重新发送fail_count值。
每当记录故障时都要执行此操作:
UPDATE tab
SET fail_count_timestamp = current_timestamp,
fail_count = CASE WHEN fail_count_timestamp
< current_timestamp - INTERVAL '1 hour'
THEN 1
ELSE fail_count + 1
END
WHERE id = 42;
查询表时,如果时间戳太旧,可以使用类似的CASE
表达式为fail_count
返回0。