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 = FalseMessage.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'] -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/57704c0c-1e1a-4841-b82e-0ebca8479637%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
