django queryset with __date returns Nones-CodePudding

I have a django queryset where I want to group by created_at date value (created_at is a datetime field).

(Activity.objects
    .values('created_at__date')
    .annotate(count=Count('id'))
    .values('created_at__date', 'count')
 )

I am following the accepted answer here which makes sense. However, the query returns None for all created_at__date values. When I change it to created_at it shows the actual value.

The generated SQL query:

SELECT 
   django_datetime_cast_date("activity"."created_at", 'UTC', 'UTC'), 
   COUNT("activity"."id") AS "count" 
FROM 
   "activity" 
GROUP BY 
   django_datetime_cast_date("activity"."created_at", 'UTC', 'UTC')

I am working with local sqlite3 database, some sample records from DB (the other columns in the table are removed):

2,2021-07-30T11:44:09.984439 00:00
3,2021-07-30T11:44:29.217916 00:00
4,2021-07-30T11:44:43.598702 00:00
5,2021-08-03T20:53:48.482419 00:00
6,2021-08-04T22:19:52.810907 00:00
7,2021-08-05T17:25:29.646553 00:00
8,2021-08-05T17:25:33.425523 00:00
9,2021-08-05T17:26:22.169369 00:00
10,2021-08-05T17:50:26.585485 00:00
11,2021-08-10T16:20:38.839126 00:00
12,2021-08-10T17:38:00.557487 00:00
13,2021-08-11T16:09:30.470890 00:00
14,2021-08-11T16:09:34.164904 00:00
15,2021-08-12T15:43:18.819458 00:00
16,2021-08-12T16:19:30.123575 00:00
18,2021-08-15T12:55:20.660137 00:00
24,2021-08-18T18:21:12.153480 00:00
25,2021-08-18T19:18:37.432911 00:00

CodePudding user response：

The reason is sqlite3 database can not store timezone information as it does not support built-in date and/or time. And since the sample date contains timezone information, sqlite3 is unable to extract date from the datetime field, and showing None.

correct format for sqlite3

2021-11-29 12:40:20.021350