Django - Mysql datetime timezone naive and aware

[Gabriel]

Hello folks, I've found an abnormal behavior migrating a project from php - mysql to django 1.6.x - mysql and timezone naive to aware datetimes, I've posted my insights about it in stack overflow, if anyone can help will be more than welcome!

Many thanks!

Stackoverflow link -> http://stackoverflow.com/questions/23755968/django-mysql-datetime-timezone-naive-and-aware

Stackoverflow Post:

I'm involved in migrating an app from php - mysql to django - mysql, well, I've started migrating an inbox app which allows users to send messages 1to1, after my first data migration (using a insert - select mysql statement) I proceed with some data migrations as follows.

• I've tried to migrate messages statuses: in my "legacy" database the approach used is using a bitwise field with integers suchs as 1 for received, 2 for read, 4 for trash and so on, I've installed django-bitwise to accomplish the same approach but I must migrate those integers to strings like "RECEIVED", "READ", "TRASH", ... since I have millions of rows I cannot use the ORM with an objects.all() and I've had decided to go month by month.

If I try to count messages by month using the following ORM statement:

Message.objects.filter(sent_at__year=2011, sent_at__month=1).count()

It returns 0

---

If I count it directly using the following SQL statement it returns the correct number:

select count(*) 
from inbox_message 
where year(sent_at) = 2011 and month(sent_at) = 1;

Result is 64955

---

After a little bit of digging into Google and Stackoverflow it seems to be something related to timezone naive datetimes, so I've tried the following

settings.USE_TZ = False
Message.objects.filter(sent_at__year=2011, sent_at__month=1).count()

Now It returns 64955 hooray!

---

Known this, I've started to convert 'sent_at' from timezone naive to timezone aware, my first approach was to apply the CONVERT_TZ mysql function in my insert - select statement but it produces the same result, since my original data source is on UTC+1 timezone I've decided to fix this as well using:

convert_tz(sent_at, '+01:00', '+00:00')

I'm not using named timezones because I didn't installed the mysql timezone tables (as docs says)

After that I've tried to use django.utils.timezone.make_aware to make my datetimes timezone aware using a simple python script... nothing changes, still having zero when USE_TZ=True, the script looks like:

from django.utils.timezone import is_naive, make_aware, utc
settings.USE_TZ = False
messages = Message.objects.filter(sent_at__year=2011, sent_at__month=1)
for m in messages:
    settings.USE_TZ = True  # Set it to true to avoid errors when datetime becomes timezone aware
    if is_naive(m.sent_at):
        m.sent_at = make_aware(m.sent_at, utc)
        m.save()

Same results, filtering by year and month returns 0 elements.

Just to let you know, filtering just by year seems to work okay.

Thanks in advance

---

Adding Model definition answering comments:

class Message(models.Model):
    subject = models.CharField(_(u"Subject"), max_length=2200)
    body = models.TextField(_(u"Body"))
    sender = models.ForeignKey(
        get_user_model(),
        related_name='sent_messages',
        verbose_name=_(u"Sender"))
    recipient = models.ForeignKey(
        get_user_model(),
        related_name='received_messages',
        null=True, blank=True, verbose_name=_(u"Recipient"))
    parent_msg = models.ForeignKey(
        'self',
        related_name='next_messages',
        null=True, blank=True,
        verbose_name=_(u"Parent message"))
    sent_at = models.DateTimeField(
        _(u"Sent at"), null=True, blank=True, db_index=True)
    read_at = models.DateTimeField(
        _(u"Read at"), null=True, blank=True, db_index=True)
    replied_at = models.DateTimeField(
        _(u"Replied at"), null=True, blank=True, db_index=True)
    sender_status = BitField(
        verbose_name=_(u'Estado sender'),
        flags=FLAGS_MESSAGE,
        db_index=True)
    recipient_status = BitField(
        verbose_name=_(u'Estado recipient'),
        flags=FLAGS_MESSAGE,
        db_index=True)

    class Meta:
        ordering = ['-sent_at']