如何查詢不同時區的日期？

我在 PostgreSQL 10.18 數據庫中有一個表和索引：

CREATE TABLE some_table (
   expires_at timestamptz
);
CREATE INDEX ON some_table(expires_at);

有沒有辦法以使用索引的方式編寫此查詢expires_at？

SELECT
   *
FROM some_table
WHERE 
   TIMEZONE('America/New_York', expires_at)::date
 < TIMEZONE('America/New_York', NOW())::date
LIMIT 5;

America/New_York作為範例添加，此查詢使用不同的時區執行。

這可以使用索引：

SELECT *
FROM   some_table
WHERE  expires_at
    < date_trunc('day', (now() AT TIME ZONE 'America/New_York')) AT TIME ZONE 'America/New_York'
-- ORDER BY expires_at  --!!?
LIMIT  5;

db<>fiddle here - 證明等價性

您可能想要添加ORDER BY expires_at或ORDER BY expires_at DESC獲得確定性結果（並且仍然使用索引）。

等等……什麼？

手冊：

該函式timezone(zone, timestamp)等效於符合 SQL 的構造時間戳AT TIME ZONE區域。

因此，這是您的查詢以更常用的形式：

SELECT *
FROM   some_table
WHERE (expires_at        AT TIME ZONE 'America/New_York')::date
   < (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date
LIMIT  5

（演員LIMIT表仍然是 Postgres 特有的，其餘的現在是標準 SQL。）

看：

• Postgres 數據類型轉換
• now() 和 current_timestamp 的區別

為了使索引適用，您需要一個“sargable”表達式，即 Postgres 必須能夠將索引項放在適用運算符的左側，並將穩定的值放在右側。看：

• PostgreSQL 可以索引數組列嗎？

用簡單的英語表達您的目標可能會有所幫助：

獲取expires_at調整到時區“America/New_York”的行位於該時區當天的 00:00 之前。

這可以分為4個步驟：

1. 取電流timestamp with time zone：

now() 2. 獲取相應timestamp without time zone的紐約本地資訊：

now() AT TIME ZONE 'America/New_York' 3. 將其截斷到一天的開始（仍然timestamp without time zone）：

date_trunc('day', (now() AT TIME ZONE 'America/New_York')) 4. 得到相應的timestamp with time zone：

date_trunc('day', (now() AT TIME ZONE 'America/New_York')) AT TIME ZONE 'America/New_York'

test=> SELECT now() AS step1
test->      , now() AT TIME ZONE 'America/New_York' AS step2
test->      , date_trunc('day', (now() AT TIME ZONE 'America/New_York')) AS step3
test->      , date_trunc('day', (now() AT TIME ZONE 'America/New_York')) AT TIME ZONE 'America/New_York' AS step4;

           step1             |           step2           |        step3        |         step4          
------------------------------+---------------------------+---------------------+------------------------
2022-05-21 19:52:34.23824+02 | 2022-05-21 13:52:34.23824 | 2022-05-21 00:00:00 | 2022-05-21 06:00:00+02
(1 row)

請記住，這timestamptz是根據會話的目前時區設置（在我的範例中為“歐洲/維也納”）顯示的，這與**值無關。

with input有兩種不同的實現AT TIME ZONEtext（加上第三種用於timetz不應該使用的 broken ）：一種用於轉置timestampto ，另一種用於轉置totimestamptz的反向操作。我的查詢同時使用兩者。timestamptz``timestamp

同樣有兩（三）個 Postgres 函式：

test=> SELECT proname AS func_name
test->      , pg_get_function_arguments(oid) AS arguments
test->      , pg_get_function_result (oid) AS result
test-> FROM   pg_proc
test-> WHERE  proname = 'timezone'
test-> AND    proargtypes[0] = 'text'::regtype;

func_name |             arguments             |           result            
-----------+-----------------------------------+-----------------------------
timezone  | text, timestamp without time zone | timestamp with time zone
timezone  | text, timestamp with time zone    | timestamp without time zone
timezone  | text, time with time zone         | time with time zone
(3 rows)

基本：

• 批量更新時間戳記錄以正確的時區
• 在 Rails 和 PostgreSQL 中完全忽略時區

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