DateField returns datetime if SQLite field is created as TIMESTAMP

[Alexandr Zarubkin]

I have SQLite database where certain column in table is created as TIMESTAMP. The database is created not by Django, but by SymmetricDS, a DB replication software. The database is replicated from Microsoft SQL Server 2005, where it has datetime type.
In my Django application, the model has DateField for that column. The returned value has datetime type, not date.

If I use the same model with Microsoft SQL Server 2005 (using django-pyodbc-azure package), the returned value has date type, even though the underlying column has datetime type.

I believe that DateField should return date always, regardless of DB backend. Please consider altering the behaviour.

[Jakub Paczkowski]

I agree that described behaviour exists. Steps to reproduce:

1. Create model like this:

class DateTimeModel(models.Model):
    date_field = models.DateField()

2. makemigrations & migrate
3. Change manually type of date_field column to TIMESTAMP
4. Add an entry with TIMESTAMP for example 2010-08-28T13:40:02.200
5. In django shell execute DateTimeModel.objects.first().date_field
6. Output is datetime.datetime(2010, 8, 28, 13, 40, 2, 200000, tzinfo=<UTC>)

However I'm not sure what should be correct behaviour- should DateField parse this as date or rather throw an exception?

[Marc Tamlyn]

Unfortunately, if django were to inspect every value of every field of every model it loaded to ensure it is in the correct format there would be a serious performance problem. Consequently care is taken to only apply db_converters when they are necessary (we are still a little heavy handed with them in some cases though).

If you wished to create a DateField subclass which would handle that, you can use from_db_value in Django 1.8 (​https://docs.djangoproject.com/en/1.8/ref/models/fields/#django.db.models.Field.from_db_value)

[Alexandr Zarubkin]

But it is somewhat counterintuitive. I didn't expect, for example, a DateField which returns datetime objects. At least it should be documented as known issue.
The reference on DateField says:

A date, represented in Python by a datetime.date instance.

On the other hand, when I use Microsoft SQL Server (with django-pyodbc-azure and pyodbc), I get date from DateField, and datetime if I change the model's field to DateTimeField. Why the same couldn't be done for SQLite?

[Aymeric Augustin]

You're merely seeing the consequences of SQLite's loose typing system. The type of a value is only determined by inspecting the value.

You wouldn't notice it if the database was managed by Django because the database would never contain values that don't cast to the correct type.

Perhaps django-pyodbc-azure performs a cast in that case, however, we don't want to do that in Django because that's a performance critical code path.

Interacting with external databases -- i.e. databases not created by Django -- requires mapping columns according to their type. Here you have a column containing datetimes. You must map it to a DateTimeField. That's all.

You can't alter the type of a column, putting the database schema out of sync with Django's model definition, and expect things to keep working.