存储日期和月份（不含年份） [英] Storing day and month (without year)

问题描述

我无法找出在数据库中存储一些数据的最佳方法。我必须在数据库中存储DD / MM日期，但我不知道最好的方法来存储，以便可以轻松地排序和搜索。

基本上，用户将能够以DD / MM格式保存重要的日期，他们将被提醒更接近当天。

DATE数据类型不似乎完全适合，因为它包括一年，但我不能想到存储这种数据的另一种方式。可能会在特定的年份结束所有场合，但这似乎是不合适的。

解决方案

我必须在数据库中存储DD / MM日期，但我不知道最好的方法来存储，以便轻松排序和搜索。

即使不要求年份组件，即使使用日期，存储日期数据的最佳方式 。当您需要使用它时，您可以删除年份，或将其替换为与（或当年）进行比较的年份。

在日期列中有助于排序正确，完整性，验证等。

为了迎合闰年，使用像'0004'这样的一年，允许'0004-02-29 '。使用第4年使得它比第0年稍微复杂一些，但作为一个例子，这将把29年（年度不可知论）日期变成今年的日期，与其他一些领域进行比较

  select 
 adddate（
 subdate（cast（'0004-02-29'as date），
 interval 4 year） ，
间隔年（curdate（））年）
 
结果：2011-02-28 
  

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.

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.

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.

解决方案

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.

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

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

result: 2011-02-28

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