Django filter date time by year, month, day, hour and minute

Question

I am aware of problems with filtering of date time in Django. That is why I build a raw Query Set:

query = 'SELECT * FROM meteorological_data_base.meteorological_data'

if year is not None:
    query += ' WHERE EXTRACT(YEAR FROM date_time) = ' + year
    if month is not None:
        query += ' AND EXTRACT(MONTH FROM date_time) = ' + month
        if day is not None:
            query += ' AND EXTRACT(DAY FROM date_time) = ' + day
            if hour is not None:
                query += ' AND EXTRACT(HOUR FROM date_time) = ' + hour
meteorological_data_list = MeteorologicalData.objects.raw(query)

I am aware of SQL injection, this was written just for test. The problem is that I need Query Set because of Pagination. Now I tried this solution and it only works if I specify just the year part:

MeteorologicalData.objects.filter(date_time__year=2010)

If I also pass in month, then it returns empty Query Set:

MeteorologicalData.objects.filter(date_time__year=2010, date_time__month=1)

As I am using Django debug toolbar I can see what kind of SQL query does Django generate. And for the last example (year and month) it generated this:

SELECT COUNT(*) FROM `meteorological_data` WHERE (EXTRACT(MONTH FROM CONVERT_TZ(`meteorological_data`.`date_time`, 'UTC', 'Europe/Ljubljana')) = 1 AND `meteorological_data`.`date_time` BETWEEN '2009-12-31 23:00:00' and '2010-12-31 22:59:59')

I tried this in mysql console and it sure returns 0 rows. If I remove the CONVERT_TZ command:

SELECT COUNT(*) FROM `meteorological_data` WHERE (EXTRACT(MONTH FROM `meteorological_data`.`date_time`) = 1 AND `meteorological_data`.`date_time` BETWEEN '2009-12-31 23:00:00' and '2010-12-31 22:59:59')

It works as it should. Now I am using timezone aware date times. My configuration in settings:

TIME_ZONE = 'Europe/Ljubljana'
USE_TZ = True

So the end question is. How to use filter command that will not convert to timezone aware date time. The data is already stored in UTC format.

EDIT: Ok, I found out that the reason is missing TIMEZONE table in MySql. But still, how do I filter without conversion to TIMEZONE?

Tom · Answer 1 · 2014-02-13T16:10:51.733

0

What happens if you shut off the USE_TZ setting? Also, (assuming you clean up the SQL to use parametized queries) you can get back a RawQuerySet using the .raw() method.

EDIT: upon review, if the problem is that the data you're querying is in UTC but the datetime you're using is not, why not just convert the datetime to UTC before running the query?

edited Feb 13 '14 at 16:10

answered Feb 13 '14 at 14:52

Tom

22,301
5
63
96

If I shut off the USE_TZ setting it works as expected. But I do need timezone aware date time in some other pages. I wish there was some kind of setting to say I want to disable it just for that particular script of code block. – Jernej Jerin Feb 13 '14 at 15:01
Just tried with RawQuerySet. The problem is that it does not have method len or count! From Pagination documentation: `Note that you can give Paginator a list/tuple, a Django QuerySet, or any other object with a count() or __len__() method. When determining the number of objects contained in the passed object, Paginator will first try calling count(), then fallback to using len() if the passed object has no count() method. This allows objects such as Django’s QuerySet to use a more efficient count() method when available.` – Jernej Jerin Feb 13 '14 at 15:08
You can wrap the result of the RawQuerySet in `list()` but be aware that will [load the whole result set into memory.](http://stackoverflow.com/questions/2317452/django-count-rawqueryset) – Tom Feb 13 '14 at 16:07
Yeah and this is the main issue. If I could load all in memory then I would probably not be using Pagination. So this is certainly not a viable option. – Jernej Jerin Feb 13 '14 at 16:30
Well, you could run the pagination by hand by passing the start and end to a LIMIT clause. Did you see my update in the question about just altering the datetime instead? – Tom Feb 13 '14 at 18:20
From Django documentation: `When USE_TZ is True, datetime fields are converted to the current time zone before filtering.` So it always converts to current timezone. On the other hand if I expect from user UTC time, I could subtract one hour on my current timezone and then pass it to the filter. This could work but is kind of hackish. – Jernej Jerin Feb 13 '14 at 19:36
Don't subtract, use pytz or the tools Django provides to localize the time. You can make a guess at the user's time zone based on their system clock (or ask them) and normalize it to UTC from there. – Tom Feb 13 '14 at 21:37

Django filter date time by year, month, day, hour and minute

1 Answers1