Reputation: 527

Problems filtering django datetime field by month and day

Can someone explain to me why the following filters are not working at the month and day level? Filtering by year seems to work, but not the other two.

>>> clicks.count()
36
>>> date = clicks[0].created
>>> date.month
2
>>> date.year
2014
>>> date.day
1
>>> clicks.filter(created__month=2)
[]
>>> clicks.filter(created__month=02)
[]
>>> clicks.filter(created__month='02')
[]
>>> clicks.filter(created__month='2')
[]
>>> clicks.filter(created__month=date.month)
[]
>>> clicks.filter(created__day=date.day)
[]

A quick update to demonstrate that I am getting the same behavior before creating and dealing with a queryset:

>>> clicks = PreviewClick.objects.filter(created__month = 2)
>>> clicks.count()
0
>>> clicks = PreviewClick.objects.filter(created__month = 02)
>>> clicks.count()
0
>>> clicks = PreviewClick.objects.filter(created__month = '02')
>>> clicks.count()
0
>>> clicks = PreviewClick.objects.filter(created__month = '2')
>>> clicks.count()
0

Here's more food for thought:

>>> clicks = PreviewClick.objects.all()
>>> counter = 0
>>> for click in clicks:
...      if click.created.month == 2:
...           counter += 1
... 
>>> counter
35

Upvotes: 26

Answers (5)

fastmultiplication

Reputation: 3081

Solution for windows, 2022 - how to make django with USE_TZ=True be able to filter by month and day, not just year. Example of now working code: queryset.filter(created__month=1)

download the timezone file from here: https://dev.mysql.com/downloads/timezones.html
make sure you get the right one - for mysql8 I used the file for 5.7
unzip it - see that it's just a sql file
load it into your mysql database. something like

mysql -u root -p mysql < FILEPATH

restart mysql service (control panel | administration tools | services | find mysql | restart)
validation: mysql | use mysql | select * from time_zone
you should see a few thousand entries. If not, it means you didn't load the sql file in correctly.
rerun django methods using filtering and it should work.

Note: this is different than varnothing's answer above because on windows, dealing with filetypes or things like mysql_tzinfo_to_sql may be different due to pathing. This one works because you fall back to clear actions like "load this sql file into this db"

Upvotes: 0

varnothing

Reputation: 1299

@Simon Wilder perfectly answer why it's not working, here is how you can actually solve it without disabling TZ support in django

Django document give instruction to install time zone definition to database:

SQLite: install pytz — conversions are actually performed in Python.

PostgreSQL: no requirements (see Time Zones).

Oracle: no requirements (see Choosing a Time Zone File).

MySQL: install pytz and load the time zone tables with mysql_tzinfo_to_sql.

In my case : mysql and Mac Os, following command solve the problem:

sudo mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -u root mysql

Upvotes: 24

devdob

Reputation: 1504

To update the answer here since I ran into the above issue but none of the solutions worked. Most new mysql installations come pre-installed with tz-info, so the mysql_tzinfo_to_sql command wont really help. And setting TZ_INFO to False isn't really a solution since many need time-zone aware datetime.

So, what worked for me was to create a tz aware datetime object and check against that. Lets say you wanna filter records for today you would do something like,

from datetime import datetime
import pytz

today = datetime.now().replace(tzinfo=pytz.UTC).date()   # tz aware datetime object
todays_records = myModel.objects.filter(created__year=today.year, created__month=today.month,created__day=today.day)

Hope this helps.

Upvotes: 0