主页 / dba / 问题 / 74917
Accepted
Jared Beck
Asked: 2014-08-26 16:21:53 +0800 CST

将 postgres 时间戳限制为“一天结束”？

  • 772

给定一个名为的表bananas
和一个名为的timestamp without time zone列end_time
， 一些 psql客户端有set timezone to 'UTC'
， 其他 psql客户端有set timezone to 'US/Eastern'
，服务器配置timezone = 'UTC'在postgresql.conf

如何编写检查约束bananas.end_time以确保end_time始终是一天的结束，定义为“美国/东部”一天的第 23 小时、第 59 分钟和第 59 秒？

我试过了：

alter table bananas
add constraint ck_end_time_is_end_of_day
check (
  23 = date_part('hour', end_time at time zone 'UTC' at time zone 'US/Eastern')
  and 59 = date_part('minute', end_time at time zone 'UTC' at time zone 'US/Eastern') 
  and 59 = floor(date_part('second', end_time at time zone 'UTC' at time zone 'US/Eastern')) 
)
;

这似乎正确地限制了列，但它似乎效率低得可怕，而且非常不可读。是否有更高效和/或更易读的实现？

postgresql constraint

3 个回答

  1. Best Answer

    目前

    虽然坚持你不幸的解决方案：

    CHECK ((end_time AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time = '23:59:59'::time)

    没错，AT TIME ZONE 两次：

    • 第一个实例将您timestamp without time zone转换为timestamp with time zone. 这是假设您实际上是在存储 UTC 时间。

    • 第二个实例将timestamptzback转换timestamp 为您给定的 time zone。现在您可以检查该time组件是否符合您的要求。

    Cast to time，而不是转换为字符串，这更便宜且更健壮。

    要摆脱小数位，您可以time(0)改为转换为，但在您的示例中，这会舍入值而不是下限。相反，用 截断date_trunc()，这是floor()使用正数的更便宜的方法：

    CHECK ((date_trunc('sec', end_time) AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time
        = '23:59:59'::time)

    适当的解决方案

    timestamp值有小数位，使用时间分量'23:59:59'作为上限是一个不幸的决定。取而代之的是，使用00:00第二天作为排他的上边界。使用检查约束来强制执行它是微不足道的。

    接下来，由于您要处理多个时区，我建议使用timestamptz而不是timestamp. timestamp内部存储与给定时区“UTC”中的存储相同，但输入/输出处理不同。

    • 时间戳在输出时自动转移到当前时区。
    • 输入带有时区偏移的时间戳，以便将值自动保存为根据 UTC 时间。

    关于 SO 的相关答案有更多细节。

    • 6

  2. 您可以使用to_char从单个函数调用中获取时间字段：

    check (to_char(end_time at time zone 'UTC' at time zone 'US/Eastern','HH24:MI:SS') = '23:59:59')

    给出的秒数SS没有四舍五入，因此应该可以等同于floor

    • 3

  3. 尝试这个。它可能更快，但仍然不可读：

    alter table bananas
add constraint ck_end_time_is_end_of_day
check (
  (end_time at time zone 'UTC' at time zone 'US/Eastern')::timestamp::date + interval '23:59:59' = 
   date_trunc('second', end_time at time zone 'UTC' at time zone 'US/Eastern')
)
;
    • 0

相关问题