date at time zone related syntax and semantic differences

Question

Question: How is query 1 "semantically" different than the query 2?

Background:

1. To extract data from the table in a db which is at my localtime zone (AT TIME ZONE 'America/New_York').
2. The table has data for various time zones such as the 'America/Los_Angeles', America/North_Dakota/New_Salem and such time zones. (Postgres stores the table data for various timezones in my local timezone)
3. So, everytime I retrieve data for a different location other than my localtime, I convert it to its relevant timezone for evaluation purposes..

Query 1:

test_db=# select count(id) from click_tb where date::date AT TIME ZONE 'America/Los_Angeles' = '2017-05-22'::date  AT TIME ZONE 'America/Los_Angeles';
 count 
-------
  1001
(1 row)

Query 2:

test_db=# select count(id) from click_tb where (date AT TIME ZONE 'America/Los_Angeles')::date = '2017-05-22'::date;
 count 
-------
     5
(1 row)

Table structure:

test_db=# /d+ click_tb
                                                               Table "public.click_tb"
              Column               |           Type           |                          Modifiers                          | Storage  | Stats target | Description 
-----------------------------------+--------------------------+-------------------------------------------------------------+----------+--------------+-------------
 id                                | integer                  | not null default nextval('click_tb_id_seq'::regclass)       | plain    |              | 
 date                              | timestamp with time zone |                                                             | plain    |              | 

Indexes:
    "click_tb_id" UNIQUE CONSTRAINT, btree (id)
    "click_tb_date_index" btree (date)

The query 1 and query 2 do not produce consistent results.
As per my tests, the below query 3, semantically addresses my requirement.
Your critical feedback is welcome.

Query 3:

test_db=# select count(id) from click_tb where ((date AT TIME ZONE 'America/Los_Angeles')::timestamp with time zone)::date = '2017-05-22'::date;

Answer 1

Do not convert the timestamp field. Instead, do a range query. Since your data is already using a timestamp with time zone type, just set the time zone of your query accordingly.

set TimeZone = 'America/Los_Angeles';
select count(id) from click_tb
where date >= '2017-01-02'
  and date <  '2017-01-03';

Note how this uses a half open interval of the dates (at start of day in the set time zone). If you want to compute the second date from your first date, then:

set TimeZone = 'America/Los_Angeles';
select count(id) from click_tb
where date >= '2017-01-02'
  and date <  (timestamp with time zone '2017-01-02' + interval '1 day');

This properly handles daylight saving time, and sargability.