存储日、月(无年)

I'm having trouble with figuring out the best way to store some data in my database. I've got to store DD/MM dates in a database, but I'm not sure of the best way to store this so that it can be easily sorted and searched.

我不知道在我的数据库中存储数据的最佳方式。我必须将DD/MM日期存储在数据库中，但我不确定最好的存储方式，以便能够轻松地对其进行排序和搜索。

Basically a user will be able to save important dates in the format DD/MM, which they will be reminded of closer to the day.

基本上，用户可以将重要的日期保存为DD/MM格式，并在临近日期时提醒用户。

The DATE data type doesn't seem completely appropriate as it includes year, but I can't think of another way of storing this data. It would be possible to include a specific year to the end of all occasions, but this almost doesn't seem right.

日期数据类型似乎并不完全合适，因为它包含年份，但是我想不出另一种存储数据的方式。有可能在所有场合结束前都包括一个特定的年份，但这似乎并不正确。

3 个解决方案

#1

I've got to store DD/MM dates in a database, but I'm not sure of the best way to store this so that it can be easily sorted and searched.

我必须将DD/MM日期存储在数据库中，但我不确定最好的存储方式，以便能够轻松地对其进行排序和搜索。

The best way to store date data, even if the year component is not required, is to use date. When you need to use it, you can remove the year, or replace it with the year being compared against (or current year).

存储日期数据的最佳方法，即使不需要年份组件，也是使用日期。当您需要使用它时，您可以删除年份，或者将其替换为被比较的年份(或当前年份)。

Having it in date column facilitates sorting correctly, integrity, validation etc.

将它放在日期列有助于正确排序、完整性、验证等。

To cater for leap years, use a year like '0004' which allows '0004-02-29'. Using year 4 makes it slightly more complicated than year 0, but as an example, this turns the date '29-Feb' (year agnostic) into a date in this year for comparison with some other field

为了迎合闰年，使用“0004”之类的年份，允许使用“0004-02-29”。使用第4年使它比第0年稍微复杂一些，但是作为一个例子，这把日期“29- 2月”(年份不可知)变成了今年的日期，以便与其他一些字段进行比较

select
    adddate(
    subdate(cast('0004-02-29' as date),
        interval 4 year),
        interval year(curdate()) year)

result: 2011-02-28

#2

Are these dates recurring? If not, how will you keep track of when one has "expired"? If the answer is "the app will manually remove the dates once they have expired", then why not simply store the DD/MM date as the next available instance of that date? For example:

这些日期的吗?如果没有，你将如何跟踪一个人的“过期”?如果答案是“应用程序将在过期后手动删除日期”，那么为什么不简单地将DD/MM日期存储为该日期的下一个可用实例呢?例如:

01/02 becomes 2012-02-01, and 04\07 becomes 2011-07-04

01/02成为2012-02-01,04\07成为2011-07-04

The built-in date/time functions are so useful that I strongly recommend you not use varchars or tinyints.

内置的日期/时间函数非常有用，因此我强烈建议您不要使用varchars或tinyints。

#3

If you really really want to drop the year, then just make TWO columns, one for day and another for month. Then store them separately.

如果你真的想放弃这一年，那就写两篇专栏，一篇是一天，另一篇是一个月。然后将它们分开。

CREATE TABLE `table-name` (
  `Day` tinyint NOT NULL,
  `Month` tinyint NOT NULL
);

But, it's much better to just use the Date type and then ignore the year in your code.

但是，最好只使用日期类型，然后忽略代码中的年份。

#1