CODEWITHSUNDEEP
pythondjangotimezoneutc
Rohit Kumar
Rohit Kumar

Reputation: 724

How to filter queryset against time in django with custom timezone?

I know the question seems stupid, but for some reason I am not able to understand why my django app does not return the correct filtering results when I change my TIME_ZONE = 'UTC' to 'TIME_ZONE = 'Asia/Kolkata''. Everything else is working just fine, but when I change the timezone to my local timezone, it does not give any error, but the function in views.py also gives 0 matching results.

This question is also related to this question.

This is my function in views.py importing the data in the Itembatch model:

@login_required
def upload_batch(request):
    template_name = 'classroom/teachers/upload.html'
    prompt = {'order':'Order of csv should be first_name, last_name, email, ip_address, message'}
    if request.method == "GET":
        return render(request,template_name,prompt)

    csv_file = request.FILES['file']
    data_set = csv_file.read().decode('UTF-8')
    io_string = io.StringIO(data_set)
    next(io_string)


    uploaded_by = request.user


    for column in csv.reader(io_string,delimiter=',',quotechar='|'):
        _, created = ItemBatch.objects.update_or_create(
            name = column[0],
            pid = column[1],
            quantity = column[2],
            length = column[3],
            width = column[4],
            height = column[5],
            volume = column[6],
            weight = column[7],
            truck_type = column[8],
            origin = column[9],
            destination = column[10],
            uploaded_by = uploaded_by
        )


    context = {}
    return render(request,template_name,context)

This is my function in views.py to render the objects :

@method_decorator([login_required, teacher_required], name='dispatch')
class UploadedItems(ListView):
    model = ItemBatch
    ordering = ('name',)
    context_object_name = 'quizzes'
    template_name = 'classroom/teachers/item_list.html'



    def get_queryset (self):
        latest_item = ItemBatch.objects.latest('time') 
        return ItemBatch.objects.filter(time__date=latest_item.time.date(), 
time__hour=latest_item.time.hour, time__minute=latest_item.time.minute)

And this is the model:

# item upload
class ItemBatch(models.Model):

    # uploaded_by = models.ForeignKey(Teacher, on_delete=models.CASCADE, related_name='uploaded_by')

    ttypes =(('Open','Open'),('Container','Container'),('Trailer','Trailer'),('All','All'))
    uploaded_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='uploaded_by')
    name = models.CharField(max_length=30)
    pid = models.CharField(max_length=30)
    quantity = models.CharField(max_length=30)
    length = models.CharField(max_length=100, blank=True)
    width = models.CharField(max_length=100, blank=True)
    height = models.CharField(max_length=100, blank=True)
    volume = models.CharField(max_length=100, blank=True)
    weight = models.CharField(max_length=100, blank=True)
    truck_type = models.CharField(max_length=255,default=0, choices=ttypes)
    origin = models.CharField(max_length=100, blank=True)
    destination = models.CharField(max_length=100, blank=True)
    time = models.DateTimeField(max_length=100, blank=True,default=now)


    def __str__ (self):
        return self.name

This is my models database:

1

What I tried

A fellow SO user suggested I should try something like this, but it did not work either.

latest_item = ItemBatch.objects.latest('time') 
from django.conf import settings
settings.USE_TZ = False 
latest_items = ItemBatch.objects.filter(time__date=latest_item.time.date(), time__hour=latest_item.time.hour, time__minute=latest_item.time.minute) 
settings.USE_TZ = True

Upvotes: 1

Views: 2835

Answers (1)

dirkgroten
dirkgroten

Reputation: 20682

The problem is that __date, __hour and __minute are using the current timezone (whatever you defined as settings.TIME_ZONE). Whereas the python datetime object retrieved from the database with latest is always in UTC.

The thing to remember when being timezone aware is: All datetime objects passed around by Django are in UTC. The timezone is in general only used when displaying these datetimes in templates (rendering to the user) or receiving input values from forms (input from the user).

The exception here are the Trunc and Extract database functions, for which __date, __hour and __minute are shortcuts. They use the settings.TIME_ZONE unless you explicitly set tzinfo to None or UTC.

So you need to be in the same timezone context for both queries. The easiest is to do the whole thing at the database level:

from django.db.models.functions import Trunc
from django.db.models import DateTimeField, Subquery 

latest = ItemBatch.objects.order_by('-time').annotate(truncated_time=Trunc(
    'time', 'minute', output_field=DateTimeField())
qs = ItemBatch.objects.annotate(truncated_time=Trunc(
    'time', 'minute', output_field=DateTimeField()))\
        .filter(truncated_time=Subquery(latest.values('truncated_time')[:1]))

Note: Make sure your queryset doesn't include any rows where the field is NULL (not possible in your case, since time cannot be NULL), that will raise an exception when calling Trunc on it.

Upvotes: 4

Related Questions