So I have this Post model. I want to be able to retrieve all posts that were created in a month, year under a certain time zone.
My goal is to implement a feature where a user anywhere in the world let's say in PST can get all posts by another person from a certain month in their time zone. So let's say user A is in EST and user B is in PST (3 hours behind EST). User B wants to see all posts that user A created in October of 2021. Since the app will display posts in the time zone the user is currently in (we send date time in UTC then the front-end converts to local time) then the app should only send to user B all posts by user A that were created in October 2021 PST. So for example if user A (the user in EST) made a post at 11pm Oct 31 2021 EST(8pm Oct 31 2021 PST) and a post at 1am Nov 1st 2021 EST (10pm Oct 31st 2021 PST) then user B should on get both posts back, because the 2nd one was made in November in EST, but October in PST.

model.py

class Post(models.Model):
    uuid = models.UUIDField(primary_key=True)
    created = models.DateTimeField('Created at', auto_now_add=True)
    updated_at = models.DateTimeField('Last updated at', auto_now=True, blank=True, null=True)
    creator = models.ForeignKey(
        User, on_delete=models.CASCADE, related_name="post_creator")
    body = models.CharField(max_length=POST_MAX_LEN)

So for example if a user creates 10 posts in November, 2 in December of 2021 in PST. Then I have a view that takes month, year and time_zone and let's say the url looks something like /post/<int:month>/<int:year>/<str:time_zone> and the user pings /post/11/2021/PST then it should return the 10 posts from November. How do I return all posts from a month and year in a time zone given time zone, month and year?

Note: The tricky edge case to take into consideration is if they post on the very last day of a month very late. Depending on the time zone something like 12/31/2021 in UTC could be 01/01/2022. Because Django stores datetime fields in UTC what would need to be done is converted created to the given time_zone then get posts from the specified month and year.

Setup:

• Django 3.2.9
• Postgresql

Attempted Solutions

• The most obvious solution to me is to convert created to the specified time_zone then to do Post.objects.filter(created__in_range=<some range>)

CodePudding user response：

I would try something like this:

Assuming you are passing a given month through as given_month and given year as given_year and given timezone as given_timezone

Model_Name.objects.filter(created.split('/',4)[2]=given_month,created.split('/',4)[3]=given_year, created.split('/',4)[4]=given_timezone)

This should get the value from the month and year and timzone section of your post. You might need to play around with what I gave you. Also it might be better to add a relationship from user to post so you can filter the user for posts with my given answer. In most cases this will be a lot more efficient, assuming there are more posts than users. This should get you on the correct track.

CodePudding user response：

Take the month's first moment (midnight on the 1st) in UTC and the next month's first moment in UTC, adjust them with the timezone you want, do a posted__range=(a, b) query?

This might work (but date math is fiddly...).

This requires python-dateutil to make computing the end time robust.

from dateutil.relativedelta import relativedelta
from django.utils import timezone

year = 2021
month = 6
tz = datetime.tzinfo("PST")

start = datetime.datetime(year, month, 1)
end = start   relativedelta(months=1)

posts = Post.objects.filter(created__range=(
  timezone.make_aware(start, tz),
  timezone.make_aware(end, tz),
))