How to increment the year on a datetimefield in Dj

问题:

Is there a way to increment the year on filtered objects using the update() method?

I am using:

python 2.6.5
django 1.2.1 final
mysql  Ver 14.14 Distrib 5.1.41

I know it's possible to do something like this:

today = datetime.datetime.today()
for event in Event.objects.filter(end_date__lt=today).iterator():
    event.start_date = festival.start_date + datetime.timedelta(365)
    event.end_date = festival.end_date + datetime.timedelta(365)
    event.save()

However, in some cases, I would prefer to use the update() method.

# This does not work..

Event.objects.all().update(
            start_date=F('start_date') + datetime.timedelta(365),
            end_date=F('end_date') + datetime.timedelta(365)
            )

With the example above, I get:

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

The sql query it's trying to make is:

UPDATE `events_event` SET `start_date` = `events_event`.`start_date` + 365 days, 0:00:00, `end_date` = `events_event`.`end_date` + 365 days, 0:00:00

I found something in the mysql guide, but this is raw sql!

SELECT DATE_ADD('2008-12-15', INTERVAL 1 YEAR);

Any idea?

回答1:

One potential cause of "Warning: Data truncated for column X" exception is the use of non-whole day values for the timedelta being added to the DateField - it is fine in python, but fails when written to the mysql db. If you have a DateTimeField, it works too, since the precision of the persisted field matches the precision of the timedelta.

I.e.:

>>> from django.db.models import F
>>> from datetime import timedelta
>>> from myapp.models import MyModel
>>> [field for field in MyModel._meta.fields if field.name == 'valid_until'][0]
<django.db.models.fields.DateField object at 0x3d72fd0>
>>> [field for field in MyModel._meta.fields if field.name == 'timestamp'][0]
<django.db.models.fields.DateTimeField object at 0x43756d0>
>>> MyModel.objects.filter(pk=1).update(valid_until=F('valid_until') + timedelta(days=3))
1L
>>> MyModel.objects.filter(pk=1).update(valid_until=F('valid_until') + timedelta(days=3.5))
Traceback (most recent call last):
...
Warning: Data truncated for column 'valid_until' at row 1
>>> MyModel.objects.filter(pk=1).update(timestamp=F('timestamp') + timedelta(days=3.5))
1L

回答2:

Quick but ugly:

>>> a.created.timetuple()
time.struct_time(tm_year=2000, tm_mon=11, tm_mday=2, tm_hour=2, tm_min=35, tm_se
c=14, tm_wday=3, tm_yday=307, tm_isdst=-1)
>>> time = list(a.created.timetuple())
>>> time[0] = time[0] + 1
>>> time
[2001, 11, 2, 2, 35, 14, 3, 307, -1]
>>>

回答3:

from dateutil.relativedelta import relativedelta

yourdate = datetime.datetime(2010, 11, 4, 10, 14, 54, 518749)
yourdate += relativedelta(years=+1)

Relativedelta takes many time arguments, from seconds to years...

回答4:

Have you seen this: Increasing a datetime field with queryset.update ? I also remember successfully using queryset .update() with timedelta with MySQL backend.