How to query dates in different timezones?



  • 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.



  • 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 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e1bd83a8d47ec406f39e500cb8d7dec0 - 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?

    https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

    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:

    • https://stackoverflow.com/a/13676871/939860
    • https://dba.stackexchange.com/questions/63548/difference-between-now-and-current-timestamp/63549#63549

    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:

    • https://stackoverflow.com/a/29245753/939860

    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 https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT-TABLE 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:

    • https://dba.stackexchange.com/questions/203890/batch-update-timestamp-records-to-correct-timezone/203919#203919
    • https://stackoverflow.com/a/9576170/939860



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2