How to query dates in different timezones?

All we need is an easy explanation of the problem, so here it is.

I have a table and index in a PostgreSQL 10.18 database:

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

Is there a way to write this query in a way to use the index on 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 is added as an example, this query is run by using different time zones.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This can use the index:

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 – proofing equivalence

You may want to add ORDER BY expires_at or ORDER BY expires_at DESC to get deterministic results (and still use the index).

Wait … what?

The manual:

The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone.

So this is your query in a more commonly used form:

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

(The cast and LIMIT are still Postgres-specific, the rest is now standard SQL.)

See:

To make the index applicable, you need a "sargable" expression, i.e. Postgres must be able to place the indexed term on the left side of an applicable operator, and a stable value to the right. See:

It may help to express your objective in plain English:
Get rows where expires_at adjusted to the time zone ‘America/New_York’ falls before 00:00 hours of the current day at that time zone.

This can be broken down into 4 steps:

  1. Take the current timestamp with time zone:

    now()

  2. Get the according local timestamp without time zone for New York:

    now() AT TIME ZONE 'America/New_York'

  3. Truncate it to the start of the day (still timestamp without time zone):

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

  4. Get the according 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)

Keep in mind that timestamptz is displayed according to the current time zone setting of your session (‘Europe/Vienna’ in my example), which has no bearing on the value whatsoever.

There are two distinct implementations of AT TIME ZONE with text input (plus a third one for the broken timetz, which shouldn’t be used): one transposing timestamp to timestamptz, and one for the reverse operation of transposing timestamptz to timestamp. My query uses both.

Likewise there are two (three) Postgres functions:

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)

Basics:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , , ,

Leave a Reply