Django filter by month and year

Question

I am trying to filter a datetime field by month and year. For some unknown reason when entering both month and year I get back an empty set returned.

Model:

class SomePost(model.Models):
    timestamp = models.DateTimeField(auto_now_add=True)

Query:

p = SomePost.objects.filter(timestamp__year=2014, timestamp__month=1)

I think it is something to do with the month but I just cannot see what i am doing wrong.

Edit

Okay so I converted the model datetimefield into a datefield and the query worked.

The resulting sql was something like this:

SELECT 
`a_post`.`id`, 
`a_post`.`title`, 
`a_post`.`slug`, 
`a_post`.`text`, 
`a_post`.`timestamp`, 
`a_post`.`published` FROM `a_post` 
WHERE EXTRACT(MONTH FROM `a_post`.`timestamp`) = 2
ORDER BY `a_post`.`timestamp` DESC;

But when i converted it back into a datetimefield the sql was:

SELECT 
`a_post`.`id`, 
`a_post`.`title`,
`a_post`.`slug`, 
`a_post`.`text`, 
`a_post`.`timestamp`,
 `a_post`.`published` FROM `a_post` 
WHERE EXTRACT(MONTH FROM CONVERT_TZ(`a_post`.`timestamp`, 'UTC', UTC)) = 1 
ORDER BY `a_post`.`timestamp` DESC

It appears as though the conver_tz is causing the issue. If I was to remove the convert_tz function when the query will return results.

Any ideas on whats going on here?

Are you certain there are records in the database for that combination of year / month? — Simeon Visser, Jan 22 '14 at 15:08
Look at http://stackoverflow.com/questions/1317714/how-can-i-filter-a-date-of-a-datetimefield-in-django - it should help you: — Michał, Jan 22 '14 at 15:11
Except for the missing 's' of 'objects', there is no way this query could be wrong, so try looking elsewhere... Have you tried with other queries ? Have you reloaded you shell since you populated your model ? — Ambroise, Jan 22 '14 at 16:29
@user204088 convert_tz is a required step when dealing with timezone aware dates in django. Not sure this is your problem. Are you sure you have good data? — Yuval Adam, Jan 22 '14 at 16:38
@YuvalAdam Yes the data is good as I can query just on year and it will work. But it fails if I include months or days. — user204088, Jan 22 '14 at 16:45
@user204088 note the discrepancy between the month in your first and second query. Where did that come from? — Yuval Adam, Jan 22 '14 at 16:56
@YuvalAdam That was just the datasets I am using both records do exist. I even run the sql in the database directly and only one retuned data. It just seems to be the django code gen portion which is failing to return the result when a datetimefield is being used. — user204088, Jan 22 '14 at 16:59
@user204088 do you have proper Django timezone settings configured? https://docs.djangoproject.com/en/dev/topics/i18n/timezones/ — Yuval Adam, Jan 22 '14 at 17:07

score 9 · Answer 1 · answered Mar 05 '18 at 06:15

9

I had same problem and this worked out for me, and i hope it will work for you.

Tested on Django-1.11.5

 p = SomePost.objects.filter(timestamp__year='2014').filter(timestamp__month='1')

more information is available here

answered Mar 05 '18 at 06:15

Jason Muriki

129
1
7

score -1 · Answer 2 · answered Nov 13 '17 at 09:48

I had the same issue in MySQL Ver 14.14 Distrib 5.6.33.

It was caused by empty time zone tables. I have fixed it by populating them:

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

It's useful to read answers on MySQL CONVERT_TZ().

Django filter by month and year

Edit

2 Answers2