How to query dates in different timezones?

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; 

fiddle - proving 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:

• Postgres data type cast
• Difference between now() and current_timestamp

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:

• Can PostgreSQL index array columns?

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:

• Batch update timestamp records to correct timezone
• Ignoring time zones altogether in Rails and PostgreSQL