在数据库中存储部分日期 [英] Storing partial dates in a database

问题描述

我想在关系数据库（MySQL，PostgreSQL等）中存储部分日期。例如，输入可能只是今年（2013）;年和月（2013-08）;或年，月，日（2013-08-29）。我不能只使用正常的DATE类型，因为年份将扩大到2013-01-01，这与年度，月份和日期无法区分。

我想到将日期分为三个不同的字段（年，月和日作为整数），但是在DBS中丢失了所有日期的好处，并且必须管理更多的指标。

$ b $我的另一个想法是将其存储为DATE，并有另一列说明日期的精确度。例如，2013-08-01和月将意味着日期只能准确到达该月（2013-08）。 2013-08-01和天意味着日期是2013-08-01。

这是最好的方法？

解决方案

我认为有两种可能的方法：

（1）存储日期的子串，如：

 '2013' 2013年
'2013-01' -  2013年1月
'2013-01-01' -  2013年1月1日
  

（2）存储 3个不同列，年，月，日（您可以建立索引年+月+没有问题的日期）

  2013 null null  - 年2013 
 2013 1 null  -  2013年1月
 2013 1 1  -  2013年1月1日
  

哪一个是最好的取决于你想如何查询数据。假设你有存储过程，并且你想传递一个参数，使所有行落入条件。

如果（1）字符串 @Date ='2013-01'作为参数，并且想要获取year = 2013和month = 01的所有行。所以 where 子句将如下：

 其中左（Date，len（@Date））= @Date 
  

如果（2），则传递三个参数 - @Year = 2013，@Month = 1，@Day = null 和其中子句将如下所示：

 其中
年= @Year和 - 假设@Year永远不为空
（@Month为null或@月份不为零，月份= @Month）和
（@Day为null或@Day不为null，Day = @Day）
  

根据您想如何处理行，可能会更复杂。例如，如果您提供参数，如 2013-01 ，是否要获取行where month = null或不？

另一方面，如果您希望通过日期并检查是否属于日期范围，那么Gordon Linoff建议是一个很好的选择。

I want to store partial dates in a relational database (MySQL, PostgreSQL, etc.). For example, input may just be the year (2013); the year and month (2013-08); or the year, month, and day (2013-08-29). I cannot just use a normal DATE type, as the year will be expanded to 2013-01-01, and this indistinguishable from having the year, month, and day.

I have thought of either separating the date into three separate fields (year, month, and day as integers), but I lose all date niceties in the DBS and have to manage more indicies.

My other thought is to store it as a DATE and have another column saying how precise the date is. For example, '2013-08-01' and 'month' would mean the date is only precise up to the month (2013-08). '2013-08-01' and 'day' would mean the date is fully 2013-08-01.

What's the best way to do this?

解决方案

I think there're two possible ways to go:

(1) Store substrings of date, like:

'2013'               -- year 2013 
'2013-01'            -- year 2013, January
'2013-01-01'         -- year 2013, January 1

(2) Store 3 different columns, Year, Month, Day (and you can build index Year + Month + Date with no proble)

2013  null  null     -- year 2013 
2013     1  null     -- year 2013, January
2013     1     1     -- year 2013, January 1st

Which one is the best depends on how do you want to query data. Suppose you have stored procedure and you want to pass a parameter to get all rows falling into condition.

In case (1), you pass string @Date = '2013-01' as a parameter and you want to get all rows where year = 2013 and month = 01. So the where clause would be like:

where left(Date, len(@Date)) = @Date

In case (2), you pass three parameters - @Year = 2013, @Month = 1, @Day = null and the where clause would be something like:

where
    Year = @Year and -- Supposing @Year is always not null
    (@Month is null or @Month is not null and Month = @Month) and
    (@Day is null or @Day is not null and Day = @Day)

It could be more complex depending on how do you want to process rows. For example, if you give parameter like 2013-01, do you want to get rows where month = null or not?

On the other hand, if you want to pass date and check if it falls into date range, then Gordon Linoff suggestion is a good one to use.

这篇关于在数据库中存储部分日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！