Sample Header Ad - 728x90

How to query dates in different timezones?

5 votes
1 answer
10795 views
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.
Asked by ffox003 (305 rep)
May 20, 2022, 04:48 PM
Last activity: Mar 14, 2025, 04:21 AM