存储只能知道年份的日期 [英] Storing a date where only the year may be known

问题描述

存储在许多情况下只能知道年份的日期值的最佳方法是什么?

What's the best way to store a date value for which in many cases only the year may be known?

MySQL 允许日期部分为零，除非启用了 NO_ZEROES_IN_DATE sql 模式，这不是默认的.如果月和日可能为零，是否有任何理由不使用日期字段，或者将其拆分为 3 个不同的字段，分别表示年、月和日(年(4)、tinyint、tinyint)?

MySQL allows zeros in date parts unless the NO_ZEROES_IN_DATE sql mode is enabled, which isn't by default. Is there any reason not to use a date field where if the month and day may be zero, or to split it up to 3 different fields for year, month and day (year(4), tinyint, tinyint)?

推荐答案

除非跨 DBMS 的可移植性很重要，否则我肯定倾向于使用单个日期字段.如果您甚至需要中等复杂的日期相关查询，那么将您的日、月和年值放在不同的字段中将变得很麻烦.

Unless portability across DBMS is important, I would definitely be inclined to use a single date field. If you require even moderately complex date related queries, having your day, month and year values in separate fields will become a chore.

MySQL 有丰富的日期相关函数 - http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html.如果您只想返回年份值，请使用 YEAR(yourdatefield)，如果您想将其包含在查询的 WHERE 子句中，请使用相同的方法.

MySQL has a wealth of date related functions - http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html. Use YEAR(yourdatefield) if you want to return just the year value, or the same if you want to include it in your query's WHERE clause.

这篇关于存储只能知道年份的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！