annotate with tz-aware datetime

plopidou · October 10, 2022, 9:57am

Hello hello!

In the django admin, I would like to annotate a row with a timezone-aware datetime.

Let’s imagine the following simplistic models:

class User(models.Model):
    name = models.CharField()

class Event(models.Model):
    user = models.ForeignKey(User)
    type = models.PositiveIntegerField()
    created_datetime = models.DateTimeField()

and in admin.py:

from django.utils.timezone import make_aware

class UserAdmin(admin.ModelAdmin):

    def event_datetime(self, obj):
        return make_aware(obj.event_datetime)
    event_datetime.admin_order_field = 'event_datetime'

    def get_queryset(self, request):
        qs = super(UserAdmin, self).get_queryset(request)
        
        qs = qs.annotate(
            event_datetime=Subquery(
                Event.objects.filter(
                    user__pk=OuterRef('pk'),
                    type=123,
                ).order_by(
                    '-created_datetime'
                ).values_list(
                    'created_datetime',
                    flat=True
                )[:1],
                output_field=models.DateTimeField()
            )
        )
        
        return qs

Now this works: the admin list view displays the list of users with a sortable “event datetime” column.

What is wrong is that the datetimes displayed are always (I think!) displayed in UTC format.

For example, the database value is (tripled-checked): 2022-10-10 09:19:38.729162
Rendered in the admin: Oct. 10, 2022, 9:19 a.m.
Should be: Oct. 10, 2022, 10:19 a.m.

A few things:

settings.TIMEZONE is set to Europe/London.

import pytz
print(pytz.timezone(settings.TIME_ZONE).__dict__)

→

{'_tzinfos': {(datetime.timedelta(days=-1, seconds=86340), datetime.timedelta(0), 'LMT'): <DstTzInfo 'Europe/London' LMT-1 day, 23:59:00 STD>, (datetime.timedelta(0), datetime.timedelta(0), 'GMT'): <DstTzInfo 'Europe/London' GMT0:00:00 STD>, (datetime.timedelta(seconds=3600), datetime.timedelta(seconds=3600), 'BST'): <DstTzInfo 'Europe/London' BST+1:00:00 DST>, (datetime.timedelta(seconds=7200), datetime.timedelta(seconds=7200), 'BDST'): <DstTzInfo 'Europe/London' BDST+2:00:00 DST>, (datetime.timedelta(seconds=3600), datetime.timedelta(0), 'BST'): <DstTzInfo 'Europe/London' BST+1:00:00 STD>}, '_utcoffset': datetime.timedelta(days=-1, seconds=86340), '_dst': datetime.timedelta(0), '_tzname': 'LMT'}

Thanks

KenWhitesell · October 10, 2022, 11:00am

See the full set of docs at Time zones | Django documentation | Django

Starting with the first paragraph:

When support for time zones is enabled, Django stores datetime information in UTC in the database, uses time-zone-aware datetime objects internally, and translates them to the end user’s time zone in templates and forms.

plopidou · October 10, 2022, 1:43pm

Hi Ken thanks.

All my TZ-related settings are indeed on.

The raw database (UTC) timestamps is 2022-10-10 09:19:38.729162.
Given the timezone Europe/London is currently at UTC+1, then why is django not displaying 2022-10-10 **10**:19:38.729162? I would have thought it would do it automatically for that annotated datetime. But instead, it seems it is always using the raw timestamps from the database without, given the timezone, adding the extra visual hour.

As in the example above, forcing the rendering to “makeaware” does not change the output client-side.

Any thoughts?

Funnily enough, the admin list view for Event above will show the created_datetime column in the proper timezone.
So there is something that I am missing for this annotated bit…

KenWhitesell · October 10, 2022, 1:53pm

Please copy/paste your timezone-related settings here - there might be something as simple as a typo involved.

plopidou · October 10, 2022, 2:05pm

USE_TZ = True
TIME_ZONE = 'Europe/London'
LANGUAGE_CODE = 'en-gb'
USE_I18N = True
USE_L10N = False #deprecated

plopidou · October 10, 2022, 2:10pm

a bit more testing…

def event_datetime(self, obj):
    if obj.event_datetime:
        print(obj.event_datetime)
        import pytz
        print(settings.TIME_ZONE)
        print(pytz.timezone(settings.TIME_ZONE).__dict__)
        dt = make_aware(obj.event_datetime, timezone=pytz.timezone(settings.TIME_ZONE))
        print(dt)
        print(localtime(make_aware(obj.event_datetime)))
        from django.utils import timezone
        print(timezone.get_default_timezone())

        return dt
    return obj.event_datetime
event_datetime.admin_order_field = 'event_datetime'

output:

2022-10-10 09:19:38.729162 # ---> from annotation ; can output_field=models.DateTimeField() be  missing the "+01:00"  bit?
Europe/London
{'_tzinfos': {(datetime.timedelta(days=-1, seconds=86340), datetime.timedelta(0), 'LMT'): <DstTzInfo 'Europe/London' LMT-1 day, 23:59:00 STD>, (datetime.timedelta(0), datetime.timedelta(0), 'GMT'): <DstTzInfo 'Europe/London' GMT0:00:00 STD>, (datetime.timedelta(seconds=3600), datetime.timedelta(seconds=3600), 'BST'): <DstTzInfo 'Europe/London' BST+1:00:00 DST>, (datetime.timedelta(seconds=7200), datetime.timedelta(seconds=7200), 'BDST'): <DstTzInfo 'Europe/London' BDST+2:00:00 DST>, (datetime.timedelta(seconds=3600), datetime.timedelta(0), 'BST'): <DstTzInfo 'Europe/London' BST+1:00:00 STD>}, '_utcoffset': datetime.timedelta(days=-1, seconds=86340), '_dst': datetime.timedelta(0), '_tzname': 'LMT'}
2022-10-10 09:19:38.729162+01:00
2022-10-10 09:19:38.729162+01:00
Europe/London

KenWhitesell · October 10, 2022, 3:02pm

What database engine are you using?

How are you checking this?

When I look at the output of an SQL SELECT statement in PostgreSQL, it shows me something like: 2022-10-10 10:39:28.48976-04.

So while the real “internal time” is UTC, the query is showing me the results as local time with the -04 offset.

plopidou · October 10, 2022, 3:06pm

database is mysql 8.xx

works perfectly for datetimes pulled “directly” out from the tables, eg:

dt = Event.objects.all().values_list(
    'created_datetime',
    flat=True,
).first()

→

datetime.datetime(2020, 5, 18, 20, 57, 9, 399761, tzinfo=<UTC>)

TZ info is there.

It does not seem to work – or I do not know how to make it work – when those timestamps are annotations for another queryset.

It is as if the annotated datetimes are “naive” datetimes.

KenWhitesell · October 10, 2022, 3:46pm

I agree, that’s how it appears to be working. However, that is definitely not the case with PostgreSQL. Annotating a value with the result of a subquery using values_list and an output_field of DateTimeField provides a timezone-aware object.

This could be a MySQL issue.

plopidou · October 10, 2022, 3:47pm

ah damn ^^
ok … is there a place where a more official kind of question could be asked?

nothing in the generated SQL looks suspicious … ?

SELECT
  `user_user`.`id`,
  (
    SELECT
      U0.`created_datetime`
    FROM
      `event` U0
    WHERE
      U0.`user_id` = `user_user`.`id`
    ORDER BY
      U0.`created_datetime` DESC
    LIMIT
      1
  ) AS `dt`
FROM
  `user_user`

KenWhitesell · October 10, 2022, 3:53pm

Looking at Databases | Django documentation | Django

I found this interesting little blurb at Databases | Django documentation | Django.

If you plan on using Django’s timezone support, use mysql_tzinfo_to_sql to load time zone tables into the MySQL database. This needs to be done just once for your MySQL server, not per database.

Have you done this?

You might also want to check out the Django issues tickets to see if there’s a related ticket - and if not, open up a ticket with a small reproduceable example.

plopidou · October 10, 2022, 4:03pm

Hi Ken, yes this was done a long time ago.

mmrrrr.

Ok well let’s try and read that list of tickets, and maybe try not to get booted when potentially submitting one

Thanks a lot!

plopidou · October 10, 2022, 5:24pm

ok it seems this is a problem in the django admin itself – at least from the (very?) few tests I have made.

Opening a shell and doing things manually, or using a quick management command results in the annotated datetimes to indeed be “TZ-aware”.

for example:

from datetime import timedelta

from django.core.management.base import BaseCommand
from django.utils import timezone
from django.db.models import OuterRef, DateTimeField

from user.models import User, Event


class Command(BaseCommand):
    """
    >>> python manage.py user_datetimes
    """

    def handle(self, *args, **options):
        now = timezone.now()
        print(f'NOW is {now}')

        # create two users
        user1 = User.objects.create(
            name='user1',
        )
        user2 = User.objects.create(
            name='user2',
        )

        # create two events each
        event1_1 = Event.objects.create(
            user=user1,
            zetype='type1',
            created_datetime=timezone.now()
        )
        event1_2 = Event.objects.create(
            user=user1,
            zetype='type2',
            created_datetime=timezone.now() - timedelta(seconds=60)
        )
        event2_1 = Event.objects.create(
            user=user2,
            zetype='type1',
            created_datetime=timezone.now()
        )
        event2_2 = Event.objects.create(
            user=user2,
            zetype='type2',
            created_datetime=timezone.now() - timedelta(seconds=60)
        )
        
        users = User.objects.distinct().annotate(
            dt_type1=Subquery(
                Event.objects.filter(
                    user__pk=OuterRef('pk'),
                    zetype='type1',
                ).order_by(
                    '-created_datetime'
                ).values_list(
                    'created_datetime',
                    flat=True
                )[:1],
                output_field=DateTimeField()
            ),
            dt_type2=Subquery(
                Event.objects.filter(
                    user__pk=OuterRef('pk'),
                    zetype='type2',
                ).order_by(
                    '-created_datetime'
                ).values_list(
                    'created_datetime',
                    flat=True
                )[:1],
                output_field=DateTimeField()
            )
        ).values(
            'name',
            'dt_type1',
            'dt_type2',
        )

        for user in users:
            print(user)

        User.objects.all().delete()

Outputs the right datetimes (tz-aware):

NOW is 2022-10-10 17:24:15.004972+00:00
{'name': 'user1', 'dt_type1': datetime.datetime(2022, 10, 10, 17, 24, 15, 18443, tzinfo=<UTC>), 'dt_type2': datetime.datetime(2022, 10, 10, 17, 23, 15, 22739, tzinfo=<UTC>)}
{'name': 'user2', 'dt_type1': datetime.datetime(2022, 10, 10, 17, 24, 15, 26726, tzinfo=<UTC>), 'dt_type2': datetime.datetime(2022, 10, 10, 17, 23, 15, 30188, tzinfo=<UTC>)}

Topic		Replies	Views
Time Zones, did I get it right? Using Django	4	905	August 27, 2020
Timezone warning from date filtering via the ORM ORM	2	3321	January 27, 2022
Make specific DateTimeFields without timezone Forms & APIs	4	2444	February 16, 2023
Trying to get a schedule time to reflect the users time zone automatically. Mystery Errors	7	1290	August 16, 2023
How to use "AT TIME ZONE" in queryset. Using the ORM	2	347	December 1, 2023

annotate with tz-aware datetime

Related Topics