F() and timedelta. Bug on django?

[Marc Aymerich]

This is a fork of this tread: 

http://groups.google.com/group/django-users/browse_thread/thread/5c6beb41fcf961a4

I'm getting troubles combining instances of F() and timedelta. I'm working with the very last trunk revision (14232),

I create a very simple model for troubleshoting the problem, the model is:

class dates(models.Model):

    date1 = models.DateField(auto_now_add=True)        

    date2 = models.DateField()  

And this is what I'm try to do: 

>>> from test.dates.dates import dates

>>> from django.db.models import F

>>> import datetime

>>> dates.objects.filter(date1__gte=F('date2')+datetime.timedelta(minutes=3))

Traceback (most recent call last):

  File "<console>", line 1, in <module>

  File "/usr/lib/python2.6/dist-packages/django/db/models/query.py", line 67, in __repr__

    data = list(self[:REPR_OUTPUT_SIZE + 1])

  File "/usr/lib/python2.6/dist-packages/django/db/models/query.py", line 82, in __len__

    self._result_cache.extend(list(self._iter))

  File "/usr/lib/python2.6/dist-packages/django/db/models/query.py", line 268, in iterator

    for row in compiler.results_iter():

  File "/usr/lib/python2.6/dist-packages/django/db/models/sql/compiler.py", line 675, in results_iter

    for rows in self.execute_sql(MULTI):

  File "/usr/lib/python2.6/dist-packages/django/db/models/sql/compiler.py", line 730, in execute_sql

    cursor.execute(sql, params)

  File "/usr/lib/python2.6/dist-packages/django/db/backends/util.py", line 18, in execute

    return self.cursor.execute(sql, params)

  File "/usr/lib/python2.6/dist-packages/django/db/backends/mysql/base.py", line 86, in execute

    return self.cursor.execute(query, args)

  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 168, in execute

    if not self._defer_warnings: self._warning_check()

  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 82, in _warning_check

    warn(w[-1], self.Warning, 3)

Warning: Truncated incorrect DOUBLE value: '0 0:3:0'

On the parent thread http://groups.google.com/group/django-users/browse_thread/thread/5c6beb41fcf961a4 Alec reports that this works on their django installation.

More over this pice of django code make use of it: http://code.djangoproject.com/attachment/ticket/10154/dateexpressions.diff

Is this really a bug? it should be reported to django "bug tracker"? 

-- 

Marc

[Alec Shaner]

It should be clarified that this occurs on the mysql backend, but not
the postgres backend. It has to do with how MySQL handles the DATETIME
object. You can't add a timedelta, because it expects a double.

I created a test app using a mysql backend and a Article model with
created and updated datetime fields.

created = 2010-10-15 09:13:02
updated = 2010-10-15 09:18:43

select created - updated from article_article
+-------------------+
| updated - created |
+-------------------+
| 541.000000 |
+-------------------+

It's using the YYYYMMDDHHMMSS format of the datetime fields:

20101015091843 - 20101015091302 = 541

The delta isn't constant either, here are two sets of times that are 5
minutes apart:

created=2010-10-15 09:00:00, updated=2010-10-15 09:05:00
mysql: select created - updated yiels 500

created=2010-10-15:09:59:00, updated=2010-10-15 10:04:00
mysql: select created - updated yields 4500

I haven't looked at the django mysql backend code, but based on this
that warning is fatal in your case because it's definitely not doing
what you want. And I don't see how you could write your query filter
in its current format so that it is postgres/mysql agnostic

> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>

[Marc Aymerich]

Wow Alec! thanks for the hard work here!! 

I inserted a print call in django code in order to see the exact query that django executes to mysql db. This is the query:

SELECT `order_order`.`id`, `order_order`.`service_id`, `order_order`.`entity_id`, `order_order`.`comment`, `order_order`.`size`, `order_order`.`included`, `order_order`.`pack_id`, `order_order`.`price`, `order_order`.`discount`, `order_order`.`renew`, `order_order`.`register_date`, `order_order`.`cancel_date`, `order_order`.`bill_id` FROM `order_order` WHERE (`order_order`.`cancel_date` IS NOT NULL AND `order_order`.`cancel_date` <  `order_order`.`register_date` + '3 0:0:0') LIMIT 21;

the problem is on the end of the query: `order_order`.`register_date` + '3 0:0:0' 

If the field is a date, Django should use addtime() function instead of a simple addition, otherwise it produces an unexpected result like you have pointed on your mail:

> created=2010-10-15 09:00:00, updated=2010-10-15 09:05:00
> mysql: select created - updated yiels 500
> 
> created=2010-10-15:09:59:00, updated=2010-10-15 10:04:00
> mysql: select created - updated yields 4500

  

 

--
Marc

[Marc Aymerich]

On Fri, Oct 15, 2010 at 5:02 PM, Alec Shaner <ash...@chumpland.org> wrote:

Instead of use datatime.timedelta I convert it to string with this format:  YYYYMMDDHHMMSS and now all works fine with mysql :) Unfortunately this part of code doesn't be database independent :( 

Thank you very much alec!

--
Marc

[Alec Shaner]

On Fri, Oct 15, 2010 at 1:26 PM, Marc Aymerich <glic...@gmail.com> wrote:
>
> Instead of use datatime.timedelta I convert it to string with this format:
>  YYYYMMDDHHMMSS and now all works fine with mysql :) Unfortunately this part
> of code doesn't be database independent :(
>
> Thank you very much alec!
>
> --
> Marc

No problem.

So if you don't mind, what does your query filter look like now using
the converted format?

[Marc Aymerich]

hi :)

this is the get_query_set method of my custom Manager:

def get_query_set(self):

    c=config.get('ignore_bill_period')

    #c is a dict like this {u'hours': u'00', u'seconds': u'00', u'minutes': u'00', u'days': u'07'}

    ignore_period = c['days']+c['hours']+c['minutes']+c['seconds']

    delta_ignore_period = datetime.timedelta(days=int(c['days']), 

        hours=int(c['hours']), minutes=int(c['minutes']), seconds=int(c['seconds']))

    now_sub_ignore = datetime.datetime.now() - delta_ignore_period

    #IF db backend is MySQL:

    return super(pending_of_billManager, self).get_query_set().filter(Q(cancel_date__isnull=False, \

        cancel_date__gt=F('register_date') + ignore_period) | Q(cancel_date__isnull=True, \

        register_date__lt=now_sub_ignore))

    #ELSE: 

    #return super(pending_of_billManager, self).get_query_set().filter(Q(cancel_date__isnull=False, \

    #    cancel_date__gt=F('register_date') + delta_ignore_period) | Q(cancel_date__isnull=True, 

    #    register_date__lt=now_sub_ignore))

a lot of code for a simple query, but it's the best I can do :) 

br
--
Marc

[Alec Shaner]

Interesting solution - after all that maybe it's more concise to just
use the 'extra' filter instead since you're making it specific to
mysql anyway, and you could use mysql date functions.

By the way, in answer to your original question on this thread, there
already is a ticket to add F() + timedelta.

http://code.djangoproject.com/ticket/10154

[Alec Shaner]

doh! Just noticed that you already referenced ticket 10154 in your
original post.

[Marc Aymerich]

On Fri, Oct 15, 2010 at 9:37 PM, Alec Shaner <ash...@chumpland.org> wrote:

doh! Just noticed that you already referenced ticket 10154 in your
original post.

Well, I referenced the patch that I found searching with google, but actually I didn't saw the ticket that it belongs to ;) 

On the ticket there is another patch that handles "F() +- timedelta" for Mysql, postgresql, sqlite and oracle :). 

http://code.djangoproject.therecom/attachment/ticket/10154/10154.diff

It looks good, tomorrow I'll test it. I hope this patch will be merged to trunk soon, then use of F() instances will have a greater sense than now :) 

br

 

--
Marc