如何处理“部分”日期（2010-00-00）从MySQL在Django？ [英] How to deal with "partial" dates (2010-00-00) from MySQL in Django?

问题描述

在我使用MySQL作为数据库的一个Django项目中，我需要一个日期字段，接受partial日期，例如年份（YYYY）和年份（YYYY- MM）加上正常日期（YYYY-MM-DD）。

In one of my Django projects that use MySQL as the database, I need to have a date fields that accept also "partial" dates like only year (YYYY) and year and month (YYYY-MM) plus normal date (YYYY-MM-DD).

MySQL中的日期字段可以通过接受 00 用于月和日。因此， 2010-00-00 在MySQL中有效，代表2010年。 2010-05-00 代表2010年5月。

The date field in MySQL can deal with that by accepting 00 for the month and the day. So 2010-00-00 is valid in MySQL and it represent 2010. Same thing for 2010-05-00 that represent May 2010.

所以我开始创建一个 PartialDateField 来支持这个功能。但是我打了一堵墙，因为默认情况下，Django使用默认的MySQLdb，python驱动到MySQL，返回 datetime.date 对象为字段和 datetime.date（）仅支持实际日期。因此，可以修改MySQLdb使用的 date 字段的转换器，并仅返回此格式'YYYY-MM-DD'的字符串。不幸的是，MySQLdb的转换器使用在连接级别设置，所以它用于所有MySQL date 字段。但是Django DateField 依赖于数据库返回 datetime.date 对象的事实，因此如果我将转换器更改为返回一个字符串，Django根本不高兴。

So I started to create a PartialDateField to support this feature. But I hit a wall because, by default, and Django use the default, MySQLdb, the python driver to MySQL, return a datetime.date object for a date field AND datetime.date() support only real date. So it's possible to modify the converter for the date field used by MySQLdb and return only a string in this format 'YYYY-MM-DD'. Unfortunately the converter use by MySQLdb is set at the connection level so it's use for all MySQL date fields. But Django DateField rely on the fact that the database return a datetime.date object, so if I change the converter to return a string, Django is not happy at all.

有人有一个想法或建议来解决这个问题？如何在Django中创建 PartialDateField ？

Someone have an idea or advice to solve this problem? How to create a PartialDateField in Django ?

另外，我应该补充说，我已经想到了2个解决方案，为年，月和日创建3个整数字段（由 Alison R 提及）或使用 varchar

Also I should add that I already thought of 2 solutions, create 3 integer fields for year, month and day (as mention by Alison R.) or use a varchar field to keep date as string in this format YYYY-MM-DD.

但是在这两种解决方案中，如果我没有错，我可以使用这个格式来保存日期格式为 YYYY-MM-DD 将会丢弃日期字段的特殊属性，例如在其上进行此类查询：在此日期之后获取所有条目。我可以在客户端重新实现这个功能，但在我的情况下，这不会是一个有效的解决方案，因为数据库可以从其他系统（mysql客户端，MS Access等）查询

But in both solutions, if I'm not wrong, I will loose the special properties of a date field like doing query of this kind on them: Get all entries after this date. I can probably re-implement this functionality on the client side but that will not be a valid solution in my case because the database can be query from other systems (mysql client, MS Access, etc.)

推荐答案

首先，感谢您的所有答案。他们都不是一个很好的解决我的问题，但为了你的辩护，我应该补充说，我没有提供所有的要求。但每个人都帮助我思考我的问题，你的一些想法是我的最终解决方案的一部分。

First, thanks for all your answers. None of them, as is, was a good solution for my problem, but, for your defense, I should add that I didn't give all the requirements. But each one help me think about my problem and some of your ideas are part of my final solution.

所以我的最后的解决方案，在数据库端，是使用 varchar 字段（限制为10个字符）并以ISO格式（YYYY-MM-DD）以字符形式存储日期，日期为 00 当没有月和/或天（如MySQL中的日期字段）。这样，该字段可以与任何数据库一起使用，数据可以由人使用简单的客户端（如mysql客户端，phpmyadmin等）直接和容易地读取，理解和编辑。这是一个要求。它也可以导出到Excel / CSV没有任何转换等。缺点是格式不强制（除了在Django）。有人可以写不是日期或在格式上犯错，数据库会接受它（如果你有这个问题的想法...）。

So my final solution, on the DB side, is to use a varchar field (limited to 10 chars) and storing the date in it, as a string, in the ISO format (YYYY-MM-DD) with 00 for month and day when there's no month and/or day (like a date field in MySQL). This way, this field can work with any databases, the data can be read, understand and edited directly and easily by a human using a simple client (like mysql client, phpmyadmin, etc.). That was a requirement. It can also be exported to Excel/CSV without any conversion, etc. The disadvantage is that the format is not enforce (except in Django). Someone could write 'not a date' or do a mistake in the format and the DB will accept it (if you have an idea about this problem...).

这样，您还可以相对轻松地完成日期字段的所有特殊查询。对于使用WHERE：<>，< =，> =和=直接工作的查询。 IN和BETWEEN查询也直接工作。对于日或月查询，你只需要使用EXTRACT（DAY | MONTH ...）。订购工作也直接。因此，我认为它涵盖了所有的查询需求，并且几乎没有复杂性。

This way it's also possible to do all of the special queries of a date field relatively easily. For queries with WHERE: <, >, <=, >= and = work directly. The IN and BETWEEN queries work directly also. For querying by day or month you just have to do it with EXTRACT (DAY|MONTH ...). Ordering work also directly. So I think it covers all the query needs and with mostly no complication.

在Django方面，我做了两件事。首先，我创建了一个 PartialDate 对象，其主要类似于 datetime.date ，但支持日期没有月和/或日。在这个对象内我使用一个datetime.datetime对象来保存日期。我使用小时和分钟作为标志，告诉它们当月和日是否有效，当它们设置为1.这是同样的想法，建议，但有一个不同的实现（只有在客户端）。使用 datetime.datetime 对象给我很多很好的功能，用于处理日期（验证，比较等）。

On the Django side, I did 2 things. First, I have created a PartialDate object that look mostly like datetime.date but supporting date without month and/or day. Inside this object I use a datetime.datetime object to keep the date. I'm using the hours and minutes as flag that tell if the month and day are valid when they are set to 1. It's the same idea that steveha propose but with a different implementation (and only on the client side). Using a datetime.datetime object gives me a lot of nice features for working with dates (validation, comparaison, etc.).

其次，我创建了一个 PartialDateField ，主要处理 PartialDate 对象和数据库之间的转换。

Secondly, I have created a PartialDateField that mostly deal with the conversion between the PartialDate object and the database.

到目前为止，它工作得很好（我大部分完成了我的广泛的单元测试）。

So far, it works pretty well (I have mostly finish my extensive unit tests).

这篇关于如何处理“部分”日期（2010-00-00）从MySQL在Django？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！