当列数据以多种类型/误差范围到达时,如何设计SQL表?

您可以将年、月和日组件存储在单独的列中。这样,您只需要填充有数据的列。

如果只是一年,那么月份和日期默认为01, YYYY-01-01

或者将其作为一个未受干扰的字符串引入,并在另一个步骤中对其进行修改以使其保持一致,或者在导入过程中修改仅限年份的值,如SQLMenace建议的那样。

我会将值存储在DATETIME类型和另一个表示其精度的值(只需要一个整数,或者某种枚举类型)。

如果您提到您将对数据执行何种查询,那么提供更多信息会更容易。

• 或者用固定的计算列存储它

像这样的

CREATE TABLE ...
   ...
   Broken varchar(20),
   Fixed AS CAST(CASE WHEN Broken LIKE '[12][0-9][0-9][0-9]' THEN Broken + '0101' ELSE Broken END AS datetime)

如果你不总是有一个完整的日期,你需要什么样的键和约束?可能存储两列数据:完整日期和年份。对于只有年份的数据,存储年份,日期为空。对于包含完整信息的项目,两者都将填充。

我在表中放了三列:

1. 提供的值(YYYY-MM-DD或YYYY)
2. 日期列、日期或日期时间数据类型,可为空

我总是填充年份列,只有当提供的值是日期时才填充日期列。

而且,因为您保留了提供的值,所以如果需要更改,您可以随时重新处理。

另一种解决方案是使用日期掩码(如IP)。将日期存储在常规的datetime字段中,并插入一个额外的smallint类型的字段或其他类型的字段,在该字段中可以指示存在哪个字段(这里甚至可以是二进制的):

如果你有 YYYY-MM-DD ,则有3位数据,如果有数据,则值为1,如果没有,则值为0。

Date         Mask
2009-12-05   7 (111)
2009-12-01   6 (110, only year and month are know, and day is set to default 1)
2009-01-20   5 (101, for some strange reason, only the year and the date is known. January has 31 days, so it will never generate an error)

哪种解决方案更好取决于你将如何处理它。

当您想要选择那些具有完整日期的日期时,这会更好,这些日期介于某个时段(要写的更少)之间。同样,这样比较任何有7,6,4等面具的日期也更容易。它还可能占用更少的内存(date+smallint可能比int+int+int小,并且只有datetime使用64位,smallint使用的内存和int一样多,才会是相同的)。

我打算提出与上面@ninesided相同的解决方案。此外,还可以有一个日期字段和一个定量表示不确定性的字段。这样做的好处是能够表示“2010年9月23日或前后”之类的内容。问题是,如果你只知道年份,你必须把日期定在年中,有182.5天的不确定性(假设是非闰年),这看起来很难看。

+各1份 ninesided , Nikki9696 和 Jeff Siver

我的解决方案:

• 仅用于完整日期的日期列
• 多年来一直使用的int列
• 确保两者之间完整性的约束
• 仅提供日期时填充年份的触发器

优势:

1. 可以在忽略缺失数据的日期列上运行简单的(一列)查询(通过使用NULL作为其设计目的)
3. 插入年份或日期或两者(前提是双方同意)
4. 不用担心专栏之间的分歧

YYYY-01-01

Sqlite 3的示例代码:

create table events
(
rowid integer primary key,
event_year integer,
event_date date,
check (event_year = cast(strftime("%Y", event_date) as integer))
);

create trigger year_trigger after insert on events
begin
update events set event_year = cast(strftime("%Y", event_date) as integer)
    where rowid = new.rowid and event_date is not null;
end;

-- various methods to insert
insert into events (event_year, event_date) values (2008, "2008-02-23");
insert into events (event_year) values (2009);
insert into events (event_date) values ("2010-01-19");

-- select events in January without expressions on supplementary columns
select rowid, event_date from events where strftime("%m", event_date) = "01";