sqlpostgresqlpostgresql-15

How can I query a timestamptz column with the timezone of the end user vs. the time zone that it is stored in

I have a table like the image below and if you notice the first row has a timestamptz value of "2023-08-13 11:31:33.013 +0000". My issue is, I need the records to show up for the user if they query for the date range between:

'2023-08-12T00:00:00.000Z' AND '2023-08-12T23:59:59.999Z'

and neither has worked as intended. The Postgresql instance is set to UTC time zone as well and it hasn't affected anything.

The table

Currently I have tried these 2 approaches:

Approach #1.

AND vs.created_at AT TIME ZONE 'America/New_York' 
    BETWEEN ('2023-08-12T00:00:00.000Z' AT TIME ZONE 'America/New_York') 
        AND ('2023-08-12T23:59:59.999Z' AT TIME ZONE 'America/New_York')`

Approach #2:

AND vs.created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' 
    BETWEEN ('2023-08-12T00:00:00.000Z' AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') 
        AND ('2023-08-12T23:59:59.999Z' AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')

I was expecting the first one to work.

Solution

  • In case you missed it, Z means UTC in ISO 8601 format. Knowing that, the way you use time zones makes little sense.

    What you want should be something like:

    vs.created_at BETWEEN
    ('2023-08-12T00:00:00.000'::timestamp  AT TIME ZONE 'America/New_York')
AND ('2023-08-12T23:59:59.999'::timestamp  AT TIME ZONE 'America/New_York')

    As you can see, converting both sides to the same time zone is unnecessary; that version says: created_at must be on Aug 12, between NY Midnight (morning, included) and NY Midnight (evening, excluded).

    Alternatively, you can make it work with dates.

        vs.created_at AT TIME ZONE 'America/New_York' >= ('2023-08-12'::date)
AND vs.created_at AT TIME ZONE 'America/New_York' <  ('2023-08-13'::date)

    In that second case, only 1 side can be converted to the desired timezone since the other side of the comparison is just dates.

    Additional note:

    You should keep in mind a confusing behavior of PostgreSQL:

    This returns a timestamp WITH time zone:

    SELECT ('2023-08-12T00:00:00.000'::timestamp  AT TIME ZONE 'America/New_York')

    whereas this retuns a timestamp WITHOUT time zone:

    SELECT ('2023-08-12T00:00:00.000'::timestamptz  AT TIME ZONE 'America/New_York')