0

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"
  • Which database is this on? Chances are your database can do the date math quite efficiently. – AKX Dec 20 '21 at 06:52
  • @AKX postgres. I've also added this information to my original post. –  Dec 20 '21 at 06:54

1 Answers1

1

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?

AKX
  • 152,115
  • 15
  • 115
  • 172
  • that's I'll give it a shot. Do you think giving time zone as just `PST` is sufficient? I thought usually it's stored with an id code in Date objects on Javascript or Python? –  Dec 20 '21 at 08:09
  • Clearly just PST works. ;-) You can use either the full name (e.g. `Europe/Helsinki`) or an abbreviation (`EET`/`EEST`), see `select * from pg_timezone_names;`... – AKX Dec 20 '21 at 08:17
  • I'm just trying to think what format would be easiest for a React Native app to retrieve locally then send and if postgres can use it. –  Dec 20 '21 at 08:29
  • 1
    Well, `Europe/Helsinki` styled timezone identifiers work too, so https://stackoverflow.com/a/59430684/51685 might work for you... – AKX Dec 20 '21 at 09:00
  • hmmm I tried it and it returns nothing unfortunately. Not sure what's going on, but `Post.objects.all()` returns stuff so I dunno. –  Dec 20 '21 at 13:56
  • `Post.objects.annotate(d=RawSQL("to_char(dt at time zone %s, 'YYYY-MM')", ['PST']),)` also returns `None`. –  Dec 20 '21 at 14:04
  • Print out the query made by the queryset for debugging - `print(qs.query)` – AKX Dec 20 '21 at 17:27
  • >>>`qs = Post.objects.annotate( d=RawSQL("to_char(dt at time zone %s, 'YYYY-MM')", ['PST']), ).values_list("d", flat=True).distinct()` >>>`qs.query` Is this what you meant? –  Dec 20 '21 at 18:07
  • >>>blah = Post.objects.annotate( d=RawSQL("to_char(dt at time zone %s, 'YYYY-MM')", ['UTC']), ).values_list("d", flat=True).distinct() >>>blah gave `django.db.utils.InternalError: current transaction is aborted, commands ignored until end of transaction block` –  Dec 20 '21 at 18:10
  • 1
    No, you explicitly need to do `print(qs.query)` to get the SQL. – AKX Dec 21 '21 at 07:45
  • 1
    And you will want to run them in a testing view or such to avoid running in a broken transaction :) – AKX Dec 21 '21 at 07:46
  • okay I added the print out to the original post. It's easier to format it that way, because there aren't formatting options in comments. –  Dec 21 '21 at 08:07
  • Well, that query seems like it should work just fine. Of course `dt` should be the name of your datetime field, e.g. `created`..? – AKX Dec 21 '21 at 09:59
  • O silly me I'll try it out. If works I'll approve your answer. –  Dec 21 '21 at 10:16
  • thanks for the help man really appreciate it –  Dec 21 '21 at 10:52
  • would you mind helping me with this similar question? https://stackoverflow.com/questions/70440712/django-given-time-zone-month-and-year-get-all-post-created-on-that-date-in-th –  Dec 21 '21 at 19:32