將 postgres 時間戳限制為“一天結束”？

給定一個名為的表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')) 
)
;

這似乎正確地限制了列，但它似乎效率低得可怕，而且非常不可讀。是否有更高效和/或更易讀的實現？

目前

雖然堅持你不幸的解決方案：

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 的相關答案有更多細節。

引用自：https://dba.stackexchange.com/questions/74917