Django - Given time zone, month and year get all post created on that date in that time zone

Question

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>)

Note
Main issue seems to be Pyzt, which takes in time in a very specific format "Amercian/Los_Angeles" w.e format this is. Rather than the abbreviated time zone format like "PST".

Answer 1

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.

import datetime
import pytz

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

year = 2021
month = 6
tz = pytz.timezone("America/Los_Angeles")

start = datetime.datetime(year, month, 1)
end = start + relativedelta(months=1)
start_as_tz_in_utc = tz.localize(start).astimezone(pytz.utc)
end_as_tz_in_utc = tz.localize(end).astimezone(pytz.utc)
print(start_as_tz_in_utc, end_as_tz_in_utc, sep="\n")

That prints out

2021-06-01 07:00:00+00:00
2021-07-01 07:00:00+00:00

which seems about right.

Then you might make a query such as

posts = Post.objects.filter(created__range=(
  start_as_tz_in_utc,
  end_as_tz_in_utc,
))

Answer 2

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.

Answer 3

Consider saving the record in UTC date time, do not attempt to save any time zone offset. just record the time the data arrived to the server in UTC, and let the browser or your app translate it to their configured timezone. It doesnt matter if your User is in China created a post and then moved to NY the next day and read the previously created post, the created_at datetime will be same for both locations because it is universal. So if the user B wants to recover the data the user A recorded since october it will as easy to querying created_at::date>="2021-10-01"

Answer 4

Sorry, if it is not very on point to what you're expecting, but Django creates a PostgreSQL timestamp with a time zone field for the DateTimeField.

# \d posts_post
                       Table "public.posts_post"
   Column   |           Type           | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
 uuid       | uuid                     |           | not null |
 created    | timestamp with time zone |           | not null |
 updated_at | timestamp with time zone |           |          |
 body       | character varying(500)   |           | not null |
Indexes:
    "posts_post_pkey" PRIMARY KEY, btree (uuid)

It means you can query them as timestamps with a custom field class from Timestamp fields in django.

from django.db import models
from datetime import datetime
from time import strftime

class UnixTimestampField(models.DateTimeField):
    """UnixTimestampField: creates a DateTimeField that is represented on the
    database as a TIMESTAMP field rather than the usual DATETIME field.
    """
    def __init__(self, null=False, blank=False, **kwargs):
        super(UnixTimestampField, self).__init__(**kwargs)
        # default for TIMESTAMP is NOT NULL unlike most fields, so we have to
        # cheat a little:
        self.blank, self.isnull = blank, null
        self.null = True # To prevent the framework from shoving in "not null".

    def db_type(self, connection):
        typ=['TIMESTAMP']
        # See above!
        if self.isnull:
            typ += ['NULL']
        if self.auto_created:
            typ += ['default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP']
        return ' '.join(typ)

    def to_python(self, value):
        if isinstance(value, int):
            return datetime.fromtimestamp(value)
        else:
            return models.DateTimeField.to_python(self, value)

    def get_db_prep_value(self, value, connection, prepared=False):
        if value==None:
            return None
        # Use '%Y%m%d%H%M%S' for MySQL < 4.1
        return strftime('%Y-%m-%d %H:%M:%S',value.timetuple())

In the query, you will pass the timestamp created from the required timezone DateTime. This approach is also should be faster since you don't need to perform 1 additional DateTime conversion.

class Post(models.Model):
    uuid = models.UUIDField(primary_key=True)
    created = UnixTimestampField(verbose_name='Created at', auto_now_add=True)
    updated_at = UnixTimestampField(verbose_name='Last updated at', auto_now=True, blank=True, null=True)


from datetime import datetime, timezone

Post.objects.filter(created__gte=datetime(year=2021, month=11, day=1, tzinfo=timezone.utc).timestamp(), created__lt=datetime(year=2021, month=12, day=1, tzinfo=timezone.utc).timestamp())