I am setting up a table where I need the year and month. In MySQL I believe I have 2 options: (1) 2 fields: 1 for year, 1 for month or (2) a date field (the day would always be 1).
我正在建立一张桌子，我需要年份和月份。在MySQL中我相信我有两个选择：（1）2个字段：1个表示年份，1个表示月份或（2）日期字段（日期总是1个）。
The 2 fields has the advantage of being some what faster (I think) because MySQL doesn't have to convert the value from a date to an integer although this is probably negligible. The date field has the advantage of "automatic" validation: someone can't get data into the db with the the month being 13 or the year being 1. With a date field you can also do date calculations more easily (ie, months between).
这两个字段的优点是更快（我认为），因为MySQL不必将值从日期转换为整数，尽管这可能是微不足道的。日期字段具有“自动”验证的优势：有人无法将数据导入数据库，月份为13或年份为1.使用日期字段，您还可以更轻松地进行日期计算（即，数月之间） ）。
Which would you use? Or is there another you would use?
你会用哪个？或者你会使用另一种吗？
10 个解决方案
#1
19
Use a date field. Since sql supports date fields natively, its easy to filter for specific dates by using the WHERE clause.
使用日期字段。由于sql本身支持日期字段，因此使用WHERE子句可以轻松过滤特定日期。
The 2 fields has the advantage of being some what faster [...]
这两个领域的优点是更快一些[...]
Your SELECT query is not your bottleneck so you shouldn't worry about this. Readability and a pragmatic program is more important than a "perceived bottleneck".
您的SELECT查询不是您的瓶颈，因此您不必担心这一点。可读性和实用程序比“感知瓶颈”更重要。
#2
1
I would use the date field even if you only need the year and month you don't lose anything by gathering all the data. As a standard practice i always gather all data when ever possible.
我会使用日期字段，即使你只需要年份和月份，你也不会因收集所有数据而丢失任何东西。作为标准做法，我总是尽可能地收集所有数据。
#3
1
I would use separate columns, mainly because that would allow for better use of indexes. E.g., I don't think an index on a datetime col will help if you are just concerned with data from a given month (not year).
我会使用单独的列，主要是因为这样可以更好地使用索引。例如，如果您只关心某个月（而非一年）的数据，我认为日期时间col的索引不会有帮助。
#4
1
Although not immediately of use to you, IBM Informix Dynamic Server supports the type:
虽然没有立即使用，但IBM Informix Dynamic Server支持以下类型：
DATETIME YEAR TO MONTH
This stores exactly what you want - the year and month. It has its uses. The DATETIME family of types includes many other types that occasionally have their uses - and some that are of marginal utility, the canonical example being DATETIME MONTH TO MINUTE. (The downside of the type is the verbose notations needed to manipulate it, but there are many operations that can be done on any or all of the DATETIME types.)
这可以存储您想要的 - 年和月。它有它的用途。 DATETIME类型系列包括偶尔有其用途的许多其他类型 - 有些具有边际效用，规范示例为DATETIME MONTH TO MINUTE。 （该类型的缺点是需要操作它的详细符号，但有许多操作可以在任何或所有DATETIME类型上完成。）
In many DBMS, you can place constraints on columns, so if you go with a two-column approach, you would place a CHECK(month_column BETWEEN 1 AND 12) constraint on the column to ensure that the user did not place an invalid value in the table. You might even apply a constraint on the year column too.
在许多DBMS中，您可以对列进行约束，因此如果采用双列方法，则会在列上放置CHECK（month_column BETWEEN 1 AND 12）约束，以确保用户未在列中放置无效值桌子。您甚至可以在年份列上应用约束。
Also, some DBMS allow you to create user-defined types, and a year-month type is pretty straight-forward as these go. The details depend on the DBMS, of course.
此外，一些DBMS允许您创建用户定义的类型，并且年月类型非常简单。当然，细节取决于DBMS。
#5
1
Unless there is a specific performance benefit of storing the year and month seperately, I would stick with the date. Regarding indexing, if have you two columns, you will need to create an index on the combination of columns rather than one for the date column. The date will be converted internally into a long value so the storage space required is not really an issue.
除非单独存储年份和月份具有特定的性能优势，否则我会坚持使用日期。关于索引，如果有两列，则需要在列的组合上创建索引，而不是为日期列创建一个索引。日期将在内部转换为长值，因此所需的存储空间实际上不是问题。
Additionally, think of the possible maintenance pain with two fields. You would have two db fields, possibly two fields on an object or the need to build/parse month and year to/from the db. Keep it simple with a date and let the DB keep track of your data integrity.
另外，考虑两个领域可能的维持疼痛。您将有两个数据库字段，可能是对象上的两个字段，或者需要构建/解析数据库中的月份和年份。使用日期保持简单，让数据库跟踪您的数据完整性。
I work with data like you described - expiration dates where day is always last day of the month so we only need month and year. We store these as a date.
我使用您描述的数据 - 到期日期，其中一天始终是该月的最后一天，因此我们只需要月份和年份。我们将这些存储为日期。
#6
1
I'd keep a datetime column and two computed columns with month and year (indexed of course). Have my cake and eat it too :)
我会保留一个datetime列和两个带月和年的计算列（当然索引）。吃我的蛋糕也吃了:)
#7
1
If you anticipate queries of the form "gimme all rows in July, regardless of the year," they'll be a little easier to write with separate month and year columns. A separate index for the month column should make it snappy.
如果您预期“7月份所有行都是gimme，无论年份如何”的查询，那么使用单独的月份和年份列来编写它们会更容易一些。月份列的单独索引应该使它变得活泼。
Otherwise, I'd go for the single date column: simple, understood, built-in validation, and date math functions work. Your only worry is that someone new to the design will wonder why everything always occurs on the first of the month.
否则，我会选择单日期列：简单，理解，内置验证和日期数学函数。您唯一担心的是，设计新手会想知道为什么一切都会在本月的第一天发生。
There is one other reason to use separate month and year columns that I've run into: when the month is not known. I've used that for apps that allow an upcoming event to be "sometime in 2009." In that case, using a NULL in the month column solves the problem nicely. There's no easy way to do that with a date-type column unless you come up with some horrible hack like the 2nd of January means the month is unknown.
我还遇到了另一个使用单独的月份和年份列的原因：当月份未知时。我已经将它用于允许即将到来的活动“在2009年的某个时间”的应用程序。在这种情况下，在月份列中使用NULL可以很好地解决问题。使用日期类型的列没有简单的方法，除非你想出一些可怕的黑客，比如1月2日意味着月份未知。
#8
1
Think about it this way: One day someone will come to you with a requirement to enhance the application with the ability to not only save year and month, but also a day. Would you then add an extra column for a day? And then, next thing, they might want you to also save time.
以这种方式思考：有一天，有人会向你提出要求增强应用程序的能力，不仅可以保存年份和月份，还可以保存一天。您是否会在一天内添加额外的列？然后，接下来，他们可能希望您也节省时间。
How easy would it be to enhance the functionality if you have separate columns for year/month/day? If you have a single date column?
如果您有年/月/日的单独列，那么增强功能会有多容易？如果您有一个日期列？
I would go for a date column for this reason alone.
因为这个原因，我会选择一个日期专栏。
#9
1
If you are going to run a lot of operations on the date field then I'd rip it apart into separate columns and deal with the data validation either in a table constraint or in the DAL.
如果要在日期字段上运行大量操作，那么我将它拆分为单独的列，并在表约束或DAL中处理数据验证。
For example, building sales reports by day, month, year are much more efficient when the fields are split. The reason being that you don't have to use datetime functions to rip apart the date for grouping.
例如，按字段分割时，按日，月，年构建销售报表会更有效。原因是您不必使用日期时间函数来分割分组日期。
If it's something like a birthday where I might query on it once in a while then I wouldn't worry about it and just leave it in a date field.
如果它像生日那样我偶尔可以查询一下，那么我就不用担心了，只需将它留在日期字段中。
#10
0
Probably not because the smallest datetime data type in SQL Server (Microsoft) is smalldatetime which is 4 bytes long. If you need just month and year then you need 1 byte for month and 2 bytes for year.
可能不是因为SQL Server（Microsoft）中最小的日期时间数据类型是4个字节长的smalldatetime。如果你只需要一个月和一年，那么你需要一个月的1个字节和一年的2个字节。
#1
19
Use a date field. Since sql supports date fields natively, its easy to filter for specific dates by using the WHERE clause.
使用日期字段。由于sql本身支持日期字段，因此使用WHERE子句可以轻松过滤特定日期。
The 2 fields has the advantage of being some what faster [...]
这两个领域的优点是更快一些[...]
Your SELECT query is not your bottleneck so you shouldn't worry about this. Readability and a pragmatic program is more important than a "perceived bottleneck".
您的SELECT查询不是您的瓶颈，因此您不必担心这一点。可读性和实用程序比“感知瓶颈”更重要。
#2
1
I would use the date field even if you only need the year and month you don't lose anything by gathering all the data. As a standard practice i always gather all data when ever possible.
我会使用日期字段，即使你只需要年份和月份，你也不会因收集所有数据而丢失任何东西。作为标准做法，我总是尽可能地收集所有数据。
#3
1
I would use separate columns, mainly because that would allow for better use of indexes. E.g., I don't think an index on a datetime col will help if you are just concerned with data from a given month (not year).
我会使用单独的列，主要是因为这样可以更好地使用索引。例如，如果您只关心某个月（而非一年）的数据，我认为日期时间col的索引不会有帮助。
#4
1
Although not immediately of use to you, IBM Informix Dynamic Server supports the type:
虽然没有立即使用，但IBM Informix Dynamic Server支持以下类型：
DATETIME YEAR TO MONTH
This stores exactly what you want - the year and month. It has its uses. The DATETIME family of types includes many other types that occasionally have their uses - and some that are of marginal utility, the canonical example being DATETIME MONTH TO MINUTE. (The downside of the type is the verbose notations needed to manipulate it, but there are many operations that can be done on any or all of the DATETIME types.)
这可以存储您想要的 - 年和月。它有它的用途。 DATETIME类型系列包括偶尔有其用途的许多其他类型 - 有些具有边际效用，规范示例为DATETIME MONTH TO MINUTE。 （该类型的缺点是需要操作它的详细符号，但有许多操作可以在任何或所有DATETIME类型上完成。）
In many DBMS, you can place constraints on columns, so if you go with a two-column approach, you would place a CHECK(month_column BETWEEN 1 AND 12) constraint on the column to ensure that the user did not place an invalid value in the table. You might even apply a constraint on the year column too.
在许多DBMS中，您可以对列进行约束，因此如果采用双列方法，则会在列上放置CHECK（month_column BETWEEN 1 AND 12）约束，以确保用户未在列中放置无效值桌子。您甚至可以在年份列上应用约束。
Also, some DBMS allow you to create user-defined types, and a year-month type is pretty straight-forward as these go. The details depend on the DBMS, of course.
此外，一些DBMS允许您创建用户定义的类型，并且年月类型非常简单。当然，细节取决于DBMS。
#5
1
Unless there is a specific performance benefit of storing the year and month seperately, I would stick with the date. Regarding indexing, if have you two columns, you will need to create an index on the combination of columns rather than one for the date column. The date will be converted internally into a long value so the storage space required is not really an issue.
除非单独存储年份和月份具有特定的性能优势，否则我会坚持使用日期。关于索引，如果有两列，则需要在列的组合上创建索引，而不是为日期列创建一个索引。日期将在内部转换为长值，因此所需的存储空间实际上不是问题。
Additionally, think of the possible maintenance pain with two fields. You would have two db fields, possibly two fields on an object or the need to build/parse month and year to/from the db. Keep it simple with a date and let the DB keep track of your data integrity.
另外，考虑两个领域可能的维持疼痛。您将有两个数据库字段，可能是对象上的两个字段，或者需要构建/解析数据库中的月份和年份。使用日期保持简单，让数据库跟踪您的数据完整性。
I work with data like you described - expiration dates where day is always last day of the month so we only need month and year. We store these as a date.
我使用您描述的数据 - 到期日期，其中一天始终是该月的最后一天，因此我们只需要月份和年份。我们将这些存储为日期。
#6
1
I'd keep a datetime column and two computed columns with month and year (indexed of course). Have my cake and eat it too :)
我会保留一个datetime列和两个带月和年的计算列（当然索引）。吃我的蛋糕也吃了:)
#7
1
If you anticipate queries of the form "gimme all rows in July, regardless of the year," they'll be a little easier to write with separate month and year columns. A separate index for the month column should make it snappy.
如果您预期“7月份所有行都是gimme，无论年份如何”的查询，那么使用单独的月份和年份列来编写它们会更容易一些。月份列的单独索引应该使它变得活泼。
Otherwise, I'd go for the single date column: simple, understood, built-in validation, and date math functions work. Your only worry is that someone new to the design will wonder why everything always occurs on the first of the month.
否则，我会选择单日期列：简单，理解，内置验证和日期数学函数。您唯一担心的是，设计新手会想知道为什么一切都会在本月的第一天发生。
There is one other reason to use separate month and year columns that I've run into: when the month is not known. I've used that for apps that allow an upcoming event to be "sometime in 2009." In that case, using a NULL in the month column solves the problem nicely. There's no easy way to do that with a date-type column unless you come up with some horrible hack like the 2nd of January means the month is unknown.
我还遇到了另一个使用单独的月份和年份列的原因：当月份未知时。我已经将它用于允许即将到来的活动“在2009年的某个时间”的应用程序。在这种情况下，在月份列中使用NULL可以很好地解决问题。使用日期类型的列没有简单的方法，除非你想出一些可怕的黑客，比如1月2日意味着月份未知。
#8
1
Think about it this way: One day someone will come to you with a requirement to enhance the application with the ability to not only save year and month, but also a day. Would you then add an extra column for a day? And then, next thing, they might want you to also save time.
以这种方式思考：有一天，有人会向你提出要求增强应用程序的能力，不仅可以保存年份和月份，还可以保存一天。您是否会在一天内添加额外的列？然后，接下来，他们可能希望您也节省时间。
How easy would it be to enhance the functionality if you have separate columns for year/month/day? If you have a single date column?
如果您有年/月/日的单独列，那么增强功能会有多容易？如果您有一个日期列？
I would go for a date column for this reason alone.
因为这个原因，我会选择一个日期专栏。
#9
1
If you are going to run a lot of operations on the date field then I'd rip it apart into separate columns and deal with the data validation either in a table constraint or in the DAL.
如果要在日期字段上运行大量操作，那么我将它拆分为单独的列，并在表约束或DAL中处理数据验证。
For example, building sales reports by day, month, year are much more efficient when the fields are split. The reason being that you don't have to use datetime functions to rip apart the date for grouping.
例如，按字段分割时，按日，月，年构建销售报表会更有效。原因是您不必使用日期时间函数来分割分组日期。
If it's something like a birthday where I might query on it once in a while then I wouldn't worry about it and just leave it in a date field.
如果它像生日那样我偶尔可以查询一下，那么我就不用担心了，只需将它留在日期字段中。
#10
0
Probably not because the smallest datetime data type in SQL Server (Microsoft) is smalldatetime which is 4 bytes long. If you need just month and year then you need 1 byte for month and 2 bytes for year.
可能不是因为SQL Server（Microsoft）中最小的日期时间数据类型是4个字节长的smalldatetime。如果你只需要一个月和一年，那么你需要一个月的1个字节和一年的2个字节。