Django DateTimeField() and timezone.now()

Question

OK, weird time zone issues when I'm running function tests. Django 1.4, Python 2.7. Are milliseconds truncated in DateTimeField() on MySQL? That's the only theory I've got.

model file

from django.db import models
from django.utils import timezone

class Search(models.Model):
    query = models.CharField(max_length=200, null=True)
    query_date = models.DateTimeField(null=True)

test.py

from django.test import TestCase
from django.utils import timezone
from search.models import Search

class SearchModelTest(TestCase):
def test_creating_a_new_search_and_saving_it_to_the_database(self):
    # start by creating a new Poll object with its "question" set
    search = Search()
    search.query = "Test"
    search.query_date = timezone.now()

    # check we can save it to the database
    search.save()

    # now check we can find it in the database again
    all_search_in_database = Search.objects.all()
    self.assertEquals(len(all_search_in_database), 1)
    only_search_in_database = all_search_in_database[0]
    self.assertEquals(only_search_in_database, search)

    # and check that it's saved its two attributes: question and pub_date
    self.assertEquals(only_search_in_database.query, "Test")
    self.assertEquals(only_search_in_database.query_date, search.query_date)

The test fails with this:

self.assertEquals(only_search_in_database.query_date, search.query_date)
AssertionError: datetime.datetime(2013, 1, 16, 21, 12, 35, tzinfo=<UTC>) != datetime.datetime(2013, 1, 16, 21, 12, 35, 234108, tzinfo=<UTC>)

I think what's happening is that the milliseconds are being truncated after saving to the database. Can that be right? I'm running MySQL v 5.5. Is MySQL truncating the date?

Answer 1

Django ORM converts DateTimeField to Timestamp in mysql. You can confirm that by looking at the raw sql doing ./manage.py sqlall <appname>

In mysql timestamp does not store milliseconds.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

It is a bug in MySql which appears to be fixed in v5.6.4, The Bug

Noted in 5.6.4 changelog.

MySQL now supports fractional seconds for TIME, DATETIME, and
TIMESTAMP values, with up to microsecond precision.