Issue with Datetime from database and Django

Question

I'm trying to generate some basic chart data, but I cannot, yet anyways. Whenever I retrieve datetime values from Django models, it gives me this output:

[print(i) for i in UserAnalyticsMeta.objects.all().values('created_at')[:3]]

{'created_at': datetime.datetime(2022, 8, 15, 22, 43, 23, 88381, tzinfo=datetime.timezone.utc)}
{'created_at': datetime.datetime(2022, 8, 15, 22, 48, 43, 944993, tzinfo=datetime.timezone.utc)}
{'created_at': datetime.datetime(2022, 8, 15, 22, 48, 49, 95255, tzinfo=datetime.timezone.utc)}

Which translates to:

2022-08-15 22:43:23.088381+00:00
2022-08-15 22:48:43.944993+00:00
2022-08-15 22:48:49.095255+00:00

However, when I try to print out the date, this is the output I get:

[print(i) for i in UserAnalyticsMeta.objects.all().values('created_at__date')[:3]]

{'created_at__date': None}
{'created_at__date': None}
{'created_at__date': None}

While I expect:

2022-08-15
2022-08-15
2022-08-15

What I've also noticed is that an old function I used also doesn't work anymore, and I feel like it has something to do with this.

        select_data = {"date_created": """strftime('%%m/%%d/%%Y', created_at)"""}
        qs = self.extra(select=select_data).values('date_created').annotate(models.Sum("page_visits"))
        return qs

Now gives me the error:

OperationalError at /admin/app_name/model_name/
(1305, 'FUNCTION app_name.strftime does not exist')

Any help would be appreciated! Thank you.

Edit

I think this is going wrong at the database level, time functions do not work properly anymore. These queries:

SELECT DATE(CONVERT_TZ(`extra_analytics_useranalyticsmeta`.`created_at`, 'UTC', 'Europe/Amsterdam')) AS `date_created` FROM `extra_analytics_useranalyticsmeta` LIMIT 3

SELECT DATE(CONVERT_TZ(`extra_analytics_useranalyticsmeta`.`created_at`, 'UTC', 'Europe/Amsterdam')) FROM `extra_analytics_useranalyticsmeta` LIMIT 3

These queries get generated by:

UserAnalyticsMeta.objects.all().annotate(date_created=TruncDate('created_at')).values('date_created')[:3].query

UserAnalyticsMeta.objects.all().values('created_at__date')[:3].query

return None everytime.

Answer 1

Sounds like Django is getting in the way.

SELECT DATE(created_at)
    FROM tablename
    WHERE ...;

If that gives you what you need, then issue it as a raw command and ignore the rest of the hassle.