如何在django中添加没有微秒的DateTimeField

I'm writing django application in django 1.8 and mysql 5.7.

我在django 1.8和mysql 5.7中编写django应用程序。

Below is the model which I have written:

下面是我写的模型:

class People(models.Model):
    name = models.CharField(max_length=20)
    age = models.IntegerField()
    create_time = models.DateTimeField()

    class Meta:
        db_table = "people"

Above model creates the table below:

以上模型创建下表:

mysql> desc people;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| name        | varchar(20) | NO   |     | NULL    |                |
| age         | int(11)     | NO   |     | NULL    |                | 
| create_time | datetime(6) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

Here Django creates datetime field with microsecond

在这里，Django以微秒创建datetime字段。

datetime(6)

datetime(6)

But I want datetime field without microsecond

但是我想要没有微秒的datetime字段

datetime

datetime

I have another application, which is also using the same database and that datetime field with microsecond is raising an issue for me.

我有另一个应用程序，它也使用相同的数据库，而带有微秒的datetime字段给我带来了一个问题。

2 个解决方案

#1

This is really very interesting question. I looked through the source code and here is the reason for setting the datetime with fractional seconds. The following snippet is from the file django/db/backends/mysql/base.py:

这是一个非常有趣的问题。我查看了源代码，这里是使用小数秒设置datetime的原因。下面的代码片段来自django/db/后端/mysql/base.py文件:

class DatabaseWrapper(BaseDatabaseWrapper):
    vendor = 'mysql'
    # This dictionary maps Field objects to their associated MySQL column
    # types, as strings. Column-type strings can contain format strings; they'll
    # be interpolated against the values of Field.__dict__ before being output.
    # If a column type is set to None, it won't be included in the output.
    _data_types = {
        'AutoField': 'integer AUTO_INCREMENT',
        'BinaryField': 'longblob',
        'BooleanField': 'bool',
        'CharField': 'varchar(%(max_length)s)',
        'CommaSeparatedIntegerField': 'varchar(%(max_length)s)',
        'DateField': 'date',
        'DateTimeField': 'datetime',
        'DecimalField': 'numeric(%(max_digits)s, %(decimal_places)s)',
        'DurationField': 'bigint',
        'FileField': 'varchar(%(max_length)s)',
        'FilePathField': 'varchar(%(max_length)s)',
        'FloatField': 'double precision',
        'IntegerField': 'integer',
        'BigIntegerField': 'bigint',
        'IPAddressField': 'char(15)',
        'GenericIPAddressField': 'char(39)',
        'NullBooleanField': 'bool',
        'OneToOneField': 'integer',
        'PositiveIntegerField': 'integer UNSIGNED',
        'PositiveSmallIntegerField': 'smallint UNSIGNED',
        'SlugField': 'varchar(%(max_length)s)',
        'SmallIntegerField': 'smallint',
        'TextField': 'longtext',
        'TimeField': 'time',
        'UUIDField': 'char(32)',
    }

    @cached_property
    def data_types(self):
        if self.features.supports_microsecond_precision:
            return dict(self._data_types, DateTimeField='datetime(6)', TimeField='time(6)')
        else:
            return self._data_types

    # ... further class methods

In the method data_types the if condition checks the MySQL version. The method supports_microsecond_precision comes from the file django/db/backends/mysql/features.py:

在方法data_type中，if条件检查MySQL版本。supports_microsecond_precision方法来自文件django/db/backends/mysql/feature。

class DatabaseFeatures(BaseDatabaseFeatures):
    # ... properties and methods

    def supports_microsecond_precision(self):                                         
        # See https://github.com/farcepest/MySQLdb1/issues/24 for the reason          
        # about requiring MySQLdb 1.2.5                                               
        return self.connection.mysql_version >= (5, 6, 4) and Database.version_info >= (1, 2, 5)

So when you use MySQL 5.6.4 or higher the field DateTimeField is mapped to datetime(6).

因此，当使用MySQL 5.6.4或更高时，字段DateTimeField将映射到datetime(6)。

I couldn't find any possibility given by Django to adjust this, so ended up with monkey patching:

我找不到Django提供的任何调整的可能性，所以最终以monkey patching结束:

from django.db.backends.mysql.base import DatabaseWrapper

DatabaseWrapper.data_types = DatabaseWrapper._data_types

Put the above code where it suits best your needs, be it models.py or __init__.py, or maybe some other file. When running migrations Django will create column datetime and not datetime(6) for DateTimeField, even if you're using MySQL 5.7.

将上面的代码放在最适合您需要的地方，不管它是模型。py或__init__。py，或者其他文件。当运行migrations Django时，将为DateTimeField创建列datetime，而不是datetime(6)，即使您正在使用MySQL 5.7。

#2

This answer gave me an idea. What if you try to manually change the migrations. First run python manage.py makemigrations and after that edit the file 0001_initial.py (or whatever the name is) in the subdirectory migrations of your app:

这个答案给了我一个主意。如果您尝试手动更改迁移怎么办?第一次运行python管理。py出境，然后编辑文件0001_initial。在应用程序的子目录迁移中:

class Migration(migrations.Migration):
    operations = [
        migrations.CreateModel(
            name = 'People'
            fields = [
                # the fields
                # ... in this part comment or delete create_time
            ],
        ),
        migrations.RunSQL(
            "ALTER TABLE people ADD COLUMN create_time datetime(0)",
            reverse_sql="ALTER TABLE people DROP COLUMN create_time",
            state_operations=[
                migrations.AddField(
                    model_name='people',
                    name='create_time',
                    fields= models.DateTimeField(),
                )
            ]
        )
    ]

This is just an example. You can try with different options and check with:

这只是一个例子。您可以尝试不同的选项，并与:

python manage.py sqlmigrations yourapp 0001

what the SQL output is. Instead of yourapp and 0001 provide the name of your app and the number of the migration.

SQL输出是什么。而不是你的应用和0001提供你的应用的名称和迁移的数量。

Here is a link to the official documentation about fractional seconds time values.

这里是有关小数秒时间值的官方文档的链接。

EDIT: I tested the code above with MySQL 5.7 and it works as expected. Maybe it can help someone else. If you get some errors, check that you have installed mysqlclient and sqlparse.

编辑:我用MySQL 5.7测试了上面的代码，它运行正常。也许它可以帮助别人。如果您得到一些错误，请检查您是否安装了mysqlclient和sqlparse。