Consider a simple DB Schema:
Table XYZ:
filter_datetime: DateTime field (With Timezone enabled)
Now, if I want to find all XYZ rows where filter_datetime occurred on "Monday", that is doable if I ignore the Timezone.
However, how do I query, if I do not want to ignore timezone information?
Also, specifically for my case, I can make sure that filter_datetime would have the same timezone. However, once the table is populated, users should be able to query according to different Timezones and get correct results. (That is a user in UTC+6:00 would have different rows when he tries to get Monday rows as opposed to a user who have timezone in UTC-6:00)
Bonus (Not really needed for me to accept the answer): Is it possible via Django ORM?
sample:
t=# create table xyz(filter_datetime timestamptz);
CREATE TABLE
t=# insert into xyz select generate_series('2018-02-04'::timestamptz,'2018-02-14'::timestamptz,'4 hour'::interval);
INSERT 0 61
select (if you don't want to ignore TZ - just don't ignore it):
t=# set TimeZone to 'UTC-6:00';
SET
t=# select ctid,filter_datetime at time zone 'UTC' utc, filter_datetime,to_char(filter_datetime,'Day'), extract(dow from filter_datetime)
from xyz
where extract(dow from filter_datetime) = 1
order by 1;
ctid | utc | filter_datetime | to_char | date_part
--------+---------------------+------------------------+-----------+-----------
(0,6) | 2018-02-04 20:00:00 | 2018-02-05 02:00:00+06 | Monday | 1
(0,7) | 2018-02-05 00:00:00 | 2018-02-05 06:00:00+06 | Monday | 1
(0,8) | 2018-02-05 04:00:00 | 2018-02-05 10:00:00+06 | Monday | 1
(0,9) | 2018-02-05 08:00:00 | 2018-02-05 14:00:00+06 | Monday | 1
(0,10) | 2018-02-05 12:00:00 | 2018-02-05 18:00:00+06 | Monday | 1
(0,11) | 2018-02-05 16:00:00 | 2018-02-05 22:00:00+06 | Monday | 1
(0,48) | 2018-02-11 20:00:00 | 2018-02-12 02:00:00+06 | Monday | 1
(0,49) | 2018-02-12 00:00:00 | 2018-02-12 06:00:00+06 | Monday | 1
(0,50) | 2018-02-12 04:00:00 | 2018-02-12 10:00:00+06 | Monday | 1
(0,51) | 2018-02-12 08:00:00 | 2018-02-12 14:00:00+06 | Monday | 1
(0,52) | 2018-02-12 12:00:00 | 2018-02-12 18:00:00+06 | Monday | 1
(0,53) | 2018-02-12 16:00:00 | 2018-02-12 22:00:00+06 | Monday | 1
(12 rows)
t=# set TimeZone to 'UTC+6:00';
SET
t=# select ctid,filter_datetime at time zone 'UTC' utc, filter_datetime,to_char(filter_datetime,'Day'), extract(dow from filter_datetime)
from xyz
where extract(dow from filter_datetime) = 1
order by 1;
ctid | utc | filter_datetime | to_char | date_part
--------+---------------------+------------------------+-----------+-----------
(0,9) | 2018-02-05 08:00:00 | 2018-02-05 02:00:00-06 | Monday | 1
(0,10) | 2018-02-05 12:00:00 | 2018-02-05 06:00:00-06 | Monday | 1
(0,11) | 2018-02-05 16:00:00 | 2018-02-05 10:00:00-06 | Monday | 1
(0,12) | 2018-02-05 20:00:00 | 2018-02-05 14:00:00-06 | Monday | 1
(0,13) | 2018-02-06 00:00:00 | 2018-02-05 18:00:00-06 | Monday | 1
(0,14) | 2018-02-06 04:00:00 | 2018-02-05 22:00:00-06 | Monday | 1
(0,51) | 2018-02-12 08:00:00 | 2018-02-12 02:00:00-06 | Monday | 1
(0,52) | 2018-02-12 12:00:00 | 2018-02-12 06:00:00-06 | Monday | 1
(0,53) | 2018-02-12 16:00:00 | 2018-02-12 10:00:00-06 | Monday | 1
(0,54) | 2018-02-12 20:00:00 | 2018-02-12 14:00:00-06 | Monday | 1
(0,55) | 2018-02-13 00:00:00 | 2018-02-12 18:00:00-06 | Monday | 1
(0,56) | 2018-02-13 04:00:00 | 2018-02-12 22:00:00-06 | Monday | 1
(12 rows)
as you see clients with different TimeZone sees different rows as Monday. This is because timestamptz keeps time in UTC - not in client time zone. Converting it when returning to client.
https://www.postgresql.org/docs/current/static/datatype-datetime.html
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
https://www.postgresql.org/docs/current/static/functions-formatting.html