函数内的时间戳比较 [英] Timestamp Comparison within Function

问题描述

为了防止旧版本覆盖新版本，在这个简单的函数中:

To prevent older version overwrite new version, in this simple function:

create function myupdate(paramts timestamp without time zone, ...)
  language plpgsql AS
$$
begin
-- step 1 compare current record timestamp vs. supplied timestamp
if exists (select 1 from record where ts <> paramts and ...) then
    raise exception 'A newer version exists, cannot update.';
end if;
...
end
$$;

ts 定义是相同的timestamp without time zone.

paramts 值由函数提供:

create function myfetch(...)
  language plpgsql AS
$$
begin
    return query select ts, ... from record where ...;
end
$$;

节点 API 和 Angular 客户端 UI 获得的是 2021-04-16T21:37:35.878Z，提交给 myupdate() 的值也是如此.但是，在我们的西海岸服务器之一，在 myupdate() 内部执行期间，ts 会自动转换为 PST 2021-04-16 14:37:35.878694 并在右侧多出 3 位数字.

What node API and Angular client UI gets is 2021-04-16T21:37:35.878Z so is the value submitted to myupdate(). However, at one of our West Coast server, during execution inside of myupdate(), ts is auto-converted to PST 2021-04-16 14:37:35.878694 and has 3 extra digits at right.

如何在 UTC 和相同精度下进行比较?

How to compare both in UTC and same precision?

推荐答案

你应该使用 timestamptz (timestamp with time zone) 而不是timestamp(timestamp without time zone)以避免任何时区混淆.在餐桌上，在功能上，在整个食物链中.值始终在内部存储为 UTC 时间，并且比较会自动正常工作(比较独立于时区的时间点).

You should use timestamptz (timestamp with time zone) instead of timestamp (timestamp without time zone) to avoid any time zone confusion. In the table, in the function, in the whole food chain. Values are always stored as UTC time internally then, and comparisons work correctly automatically (comparing points in time independent of time zones).

见:

• 在 Rails 和 PostgreSQL 中完全忽略时区

无论哪种方式，两种类型都具有微秒分辨率，即6 派系十进制数字.您的第一个示例以某种方式被截断了，可能是您的客户端在显示中截断了.

Either way, both types have microsecond resolution, i.e. 6 factional decimal digits. Your first example got truncated somehow, possibly by your client in the display.

这篇关于函数内的时间戳比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！