Django - Return a list of months that a query of Post objects spans to front-end in local time-CodePudding

So I have kind of a weird and interesting problem. Lets say I want to be able to group posts on an application like Facebook by months in local time to whoever sent the request. Let's say I have a Post table in Django with the standard created, body and author fields. Where created is the date and time it is created.
Let's say I want to find all posts by a user so that query would look like Post.objects.filter(author=<some name>). I want to be able to send back to the requester for these posts the dates they span. The reason this is slightly tricky is because Django stores in UTC and has no notion of user's time zone unless given.
My initial idea would be make a url that's something like /post/<str:author>/<str:timezone> then somehow convert created date for all queried Post to that time zone and figure out the months. The timezone is needed to cover the edge case that someone posts on the last day of the month. Depending on the time zone that could be the current month in UTC or the next.

How would I find all the months in the requestors timezone that Post span that are by an author?

So for example if someone pings the url: /post/James/PST and let's say James posted 10 times in November and 20 times in October in PST. The response should return ['October', 'November']

I'm currently running on:

Django 3.2.9
Postgresql

Attempted Solutions

>>>qs = Post.objects.annotate(
            d=RawSQL("to_char(dt at time zone %s, 'YYYY-MM')", [time_zone]),
        ).values_list("d", flat=True).distinct()
>>>print(qs.query)
SELECT DISTINCT (to_char(dt at time zone PST, 'YYYY-MM')) AS "d" FROM "cheers_post"

CodePudding user response：

I'd say you'll need to drop down to some Postgres specifics, but this seems to work.

You can see my client timezone is 03:00 (Finland) in the select * and the data saved was in UTC (Z).

akx=# create table x (id serial, dt timestamptz);
CREATE TABLE
akx=# insert into x (dt) values ('2021-06-01T00:00:00Z');
INSERT 0 1
akx=# select * from x;
 id |           dt
---- ------------------------
  1 | 2021-06-01 03:00:00 03
(1 row)
akx=# select distinct to_char(dt at time zone 'PST', 'YYYY-MM') from x;
 to_char
---------
 2021-05
(1 row)
akx=#

Translated to Django, maybe

tz = "PST"
post_dates = Post.objects.annotate(
  d=RawSQL("to_char(dt at time zone %s, 'YYYY-MM')", [tz]),
).values_list("d", flat=True).distinct()

or thereabouts?