SQL 日期模式用于用户可能不知道所有信息的日期
您的系统允许新用户注册并输入他们的婚礼日期。 作为一个纯粹主义者，我想将其存储为日期。 经过用户访谈后，很明显现实与这种方法不同。 用户通常不确定日期或月份，并希望存储部分日期，例如“2009 年 6 月”或“2012 年的某个时间”。
有谁知道一个好的设计模式，允许存储部分日期，然后尝试为重要的一天创建时间线 - 即使输入了部分日期？
You system allows new users to register and enter their wedding date. Being a purist, I wanted to store this as a date. After user interviews, it was clear that the reality differs from this approach. Users are often not sure of the day or the month, and wish to store partial dates, so "June 2009" or "sometime in 2012".
Does anyone know a good design pattern that allows the storing partial dates, and then trying to create a timeline for the big day - even though there are partial dates entered?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您可以将其存储为具有晚于日期和早于日期列的范围。
You could store it as a range with an after-date and before-date column.
也许是一个“模糊”的约会。 如果他们说“2009 年 6 月”，则可以存储为 2009 年 6 月 15 日，差异为 +/- 15 天。
“2010 年的某个时间”将是 2010 年 7 月 1 日，差异为 180 天。
因此存储一个日期和 # 天。
我不知道这在实践中是否/如何发挥作用。 只是一个想法。 日期范围可能更容易实施。
Maybe a "fuzzy" date. If they say "June 2009", that could be stored as 15 June 2009, with a variance of +/- 15 days.
"sometime in 2010" would be 01 July 2010 with a variance of 180 days.
So store a date and a # days.
If/how that would work in practice I don't know. Just an idea. A date range might be easier to implement.
您始终可以将日期存储在单独的列中：
为空。 您可以添加一个列来存储计算出的全职时间。 对于该计算时间，您对所有为空的列使用一些默认值。
You can always store the date in separate columns:
Keep them all nullable. You can add a column where you store the computed full time. For that computed time you use some default values for all the columns that are null.
您可以将日期存储为
YYYYMMDD，并将未知月/日设置为 0。例如，20090200将表示“2009 年 2 月的某天”。 这将使您能够相对轻松地进行排序和比较，如果您需要将其转换为日期，您只需在月份或日期上加 1 即可获得更高级的日期比较的近似值。You could store dates as
YYYYMMDD, with unknown months/days set to 0. e.g.,20090200would be "some day in feb 2009". This will all you to sort and compare relatively easily, and if you need to convert it to a date, you can just add 1 to the month or day to get an approximation for more advanced date comparisions.我对日期也有类似的问题； 还有一个问题是，有些人还想说“6 月 15 日”，但没有具体说明年份。 这也是你必须处理的案子吗？
我将日期、月份和年份存储在数据库中的三个单独的列中，每个列都可为空，然后（在某种程度上）在我的应用程序中重新发明了轮子（以及不允许某些情况，例如人们说“1974 年 15 日”） ）。
但是，当我将所有日期处理逻辑放入应用程序中时，我从未单独访问过这三列。 事后看来，我希望将它们放在一个列中，将部分日期作为字符串（例如“19740615”、“1974----”、“----0615”等），这样我就可以' d 列杂乱较少。
我想这取决于您想在数据库中执行多少操作以及您想在应用程序中执行多少操作。 不管怎样，我认为你会发明一个形状略有不同的轮子。 但是，当然，您足以制作一些整洁的、封装的、可重用的类 - 这会让它变得有趣！ :-)
I had a similar problem with dates; with the added issue that some people also wanted to say "15 June", without specifying the year. Is this a case you have to handle too?
I stored the day, month and year in three separate columns in the database, with each nullable, then (somewhat) re-invented the wheel in my application (as well as disallowing some cases, such as people saying "The 15th of 1974").
However, as I put all the date-handling logic in my application, I never accessed the three columns individually. With hindsight I wished I had put them in a single column, holding partial dates as strings (something like "19740615", "1974----", "----0615", etc.), just so that I'd have less column clutter.
I guess it depends how much you want to do in the database and how much you want to do in your application. Either way, I think you'll be inventing a slightly differently-shaped wheel. But of course, you're good enough to make some tidy, encapsulated, reusable classes - that'll make it fun! :-)