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 django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. 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.
