-1

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.

Rami
  • 490
  • 7
  • 22
  • 1
    Well `2023-08-13 11:31:33.013 +0000` is not in the range `'2023-08-12T00:00:00.000Z' AND '2023-08-12T23:59:59.999Z'` so it will not work. – Adrian Klaver Aug 13 '23 at 15:15
  • New York is only 4 hours behind UTC. 4 hours is not enough of a difference to drive 11:31 into the previous day. Why do you think it is? – jjanes Aug 13 '23 at 16:24
  • @AdrianKlaver Did you see every other Row that is within that range? – Rami Aug 13 '23 at 16:32
  • 1
    Your request is *... the first row has a timestamptz value of "2023-08-13 11:31:33.013 +0000". My issue is, I need the record to show up for the user ...* and that is not going to happen. If you want an answer you need to one 1) Not use images for data, copy and paste as text. 2) Provide example data showing expected output. – Adrian Klaver Aug 13 '23 at 16:46
  • 1
    1) `select '2023-08-12 23:16:12 +0000'::timestamptz between ('2023-08-12T00:00:00.000Z'::timestamptz) AND ('2023-08-12T23:59:59.999Z'::timestamptz); 't'` 2) Read [Date/time](https://www.postgresql.org/docs/current/datatype-datetime.html): Section *8.5.1.3. Time Stamps*. – Adrian Klaver Aug 13 '23 at 16:53

1 Answers1

1

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')
Atmo
  • 2,281
  • 1
  • 2
  • 21
  • I have now added ::timestamp to my queries and it appears to have solved the problem. Thank you for this! – Rami Aug 13 '23 at 17:11
  • are you sure I shouldn't convert the vs.created_at to the same time zone since it is in UTC in the database? – Rami Aug 19 '23 at 01:05
  • Not sure what you mean by that question. When you compare 2 `timestamptz` they do not need to be on the same time zone. Demonstration: `SELECT ('2023-08-12T04:30:00.000'::timestamp AT TIME ZONE 'UTC') < ('2023-08-12T00:00:00.000'::timestamp AT TIME ZONE 'America/New_York') UNION SELECT ('2023-12-12T04:30:00.000'::timestamp AT TIME ZONE 'UTC') < ('2023-12-12T00:00:00.000'::timestamp AT TIME ZONE 'America/New_York')` (Same time, same 2 time zones in both records but different dates hence different UTC offset in NY). – Atmo Aug 19 '23 at 01:15
  • I mean since the column is stored in UTC and the end-user is in EDT shouldn't I conver the vs.created_at to EDT first i.e. ```vs.created_at AT TIME ZONE 'America/New_York'``` The idea is I want the end-user to only view the data that occurred in their 24-hour day span and not any data that occurred outside of it. – Rami Aug 19 '23 at 01:20
  • Are you talking about the alternative solution I mentioned (using `::date` in the `WHERE` clause)? Or about converting `created_at` to the right time zone in the `SELECT` clause? In the latter case, you can execute `SET TIME ZONE 'America/New_York';` when your user connects so you do not have to bother with the display. – Atmo Aug 19 '23 at 01:35
  • ah I see, you did mention that, sorry I am a bit slow. How about in the select itself, I noticed I need to convert it to the timezone as well otherwise it comes back in UTC format, is that an accurate assumption? i.e. ``` Select vs.created_at AT TIME ZONE 'America/New_York' from ...``` Is that the correct way or do I assume the conversion holds from the where clause? – Rami Aug 19 '23 at 01:48
  • Try that or try the preemptive `SET TIME ZONE` – Atmo Aug 19 '23 at 01:59
  • I can't do set time zone since we have users from literally every time zone. – Rami Aug 19 '23 at 02:01