Filter by month not working

[ivancho1707@…]

Hello

I recently migrated from 1.4.2 to 1.6.3 and since then a some queries are not returning results. I debugged those and find that it happens whenever I try to filter an entity by month.

transactions = Transaction.objects.filter(created_date__month=some_date.month) 
print transactions.count()

this returns 0 results always.

Using Django Toolbar I found what kind of query was executing behind and it was something like the following (BTW I am using mysql as db engine and I already set Time Zone Tables.)

SELECT ... FROM `app_transaction` WHERE EXTRACT(MONTH FROM CONVERT_TZ(`app_transaction`.`created_date`, 'UTC', 'America/Mexico_City')) = 6)

which differs from docs for this version:

SELECT ... WHERE EXTRACT('month' FROM pub_date) = '12';

well I'm not really fond to databases but it looks like it's converting to my local timezone before getting all transactions that were made in June of any year, it looks like it must work to me.

In 1.4.2 did it exactly like the example, so I assume there's something weird in the query and needs to be fixed

SELECT ... FROM `app_transaction` WHERE EXTRACT(MONTH FROM `app_transaction`.`created_date`) = 6)

Cheers

[anonymous]

Hello

I kept researching what went wrong and discover what could really be causing the problem. What I found made me more confused.

I did the same as before:

transactions = Transaction.objects.filter(created_date__month=6) 
print transactions.count()

which in mysql resulted to be:

SELECT COUNT(*) FROM `app_transaction` WHERE EXTRACT(MONTH FROM CONVERT_TZ(`app_transaction`.`created`, 'UTC', 'America/Mexico_City')) = 6

and outputs 0 in my django project and in mysql console. Then by chance I retried the same query in mysql console changing COUNT(*) to count(*):

SELECT COUNT(*) FROM `cinema_transaction` WHERE EXTRACT(MONTH FROM CONVERT_TZ(`cinema_transaction`.`created`, 'UTC', 'America/Mexico_City')) = 6

which output was 4900, the expected result. The same happened with similar queries which involved filtering results by month and used COUNT, SUM and AVG, they returned 0 or None and when I rewrote the mentioned functions in mysql console to lower case they worked as expected.

This behavior doesn't occur when using othe filters than datemonth, the queries produced by django work as they are, with these functions in upper case.

I tested this in Ubuntu and Mac and it only happened in Ubuntu.

Could it be a misbehavior of mysql? Can you confirm that?

---

Server version: 5.5.27 MySQL Community Server (GPL) <- this worked
Server version: 5.5.37-0ubuntu0.12.04.1 (Ubuntu) <- this didn't

[Tim Graham]

​Time zone-aware day, month, and week_day lookups is documented as a backwards incompatible change, so it's not surprising the behavior would change. Please be sure you have read ​all the documentation on time zones and use support channels per the link below to get help if you still have problems. If, after doing that, you believe there is a bug in Django, please reopen this ticket.

https://code.djangoproject.com/wiki/TicketClosingReasons/UseSupportChannels