存储不准确/不精确日期的好方法是什么？

我建议将数据保留为类型。然后在另一列中存储该日期的类型。例如，如果您存储它可能是“没有确切的日期，但我们知道它是在一月份”，或者您知道确切的日期和时间。DateTime2023-01-01 12:00:00

显示时，使用第二列设置 DateTime 的格式。例如

DateTime              | DateType
2023-01-01 12:00:00     DateOnly -> Ignore the time part
2023-01-01 12:00:00     DateTime -> You exactly know the Date and the Time
2023-03-30 11:59:59     Q1 -> Ignore the time part
2023-03-30 11:59:59     Q1 -> You exactly know the Date and the Time

执行上述操作将减少数据插入/更新/删除操作，并且数据搜索将变得更加容易。您只需要处理数据过滤器和呈现逻辑。哪个会更简单。

拉哈图尔的答案很好，只是缺乏实施细节。

要存储大致日期，您需要两条信息：

• 近似值本身，作为 .DATE
• 这个近似值有多精确。

在您的问题中，您提到了多种选择。让我们将它们填充到枚举中，以便编写文档（并在 C# 代码中使用），并添加更多解析选项：

enum EDatePrecision
{
    Century = 1,
    Decade = 2,
    Year = 3,
    Quarter = 4,
    Month = 5,
    Week = 6,
    Day = 7
}

我们会将精度存储为数据库中的 a。TINYINT

接下来，我们需要一种方法来确定近似值所涵盖的有效值的范围。这在 SQL 中并不难，因此我们将定义几个 UDF 来完成这项工作。在MSSQL中，可能是这样的：

CREATE FUNCTION dbo.ApproxDateLowerBound(@when DATE, @prec TINYINT)
RETURNS DATE
AS
BEGIN
    RETURN CASE @prec
        WHEN 1 THEN DATEFROMPARTS((YEAR(@when) / 100) * 100, 1, 1)
        WHEN 2 THEN DATEFROMPARTS((YEAR(@when) / 10) * 10, 1, 1)
        WHEN 3 THEN DATEFROMPARTS(YEAR(@when), 1, 1)
        WHEN 4 THEN DATEFROMPARTS(YEAR(@when), CASE DATEPART(quarter, @when) WHEN 1 THEN 1 WHEN 2 THEN 4 WHEN 3 THEN 7 ELSE 10 END, 1)
        WHEN 5 THEN DATEFROMPARTS(YEAR(@when), MONTH(@when), 1)
        WHEN 6 THEN DATEADD(d, 1 - DATEPART(WEEKDAY, @when), @when)
        ELSE @when
    END;
END;

CREATE FUNCTION FUNCTION dbo.ApproxDateUpperBound(@when DATE, @prec TINYINT)
RETURNS DATE
AS
BEGIN
    RETURN CASE @prec 
        WHEN 1 THEN DATEFROMPARTS((YEAR(@when) / 100) * 100 + 99, 12, 31)
        WHEN 2 THEN DATEFROMPARTS((YEAR(@when) / 10 ) * 10 + 9, 12, 31)
        WHEN 3 THEN DATEFROMPARTS(YEAR(@when), 12, 31)
        WHEN 4 THEN DATEADD(d, -1 , DATEADD(m, 3, DATEFROMPARTS(YEAR(@when), CASE DATEPART(quarter, @when) WHEN 1 THEN 1 WHEN 2 THEN 4 WHEN 3 THEN 7 ELSE 10 END, 1)))
        WHEN 5 THEN DATEADD(d, -1, DATEFROMPARTS(YEAR(@when), MONTH(@when) + 1, 1))
        WHEN 6 THEN DATEADD(d, 7 - DATEPART(WEEKDAY, @when), @when)
        ELSE @when
    END;
END;

现在，您可以获得一些定义的近似边界并用于比较。BETWEEN

---

几点说明...

DATETIME值

显然，上面的解决方案仅适用于日期（请注意我使用的所有那些地方，而不是）。它可以扩展以涵盖小时、刻钟、分钟、秒的额外精度步骤......随心所欲。逻辑应该相当清楚。DATEDATETIME

其他近似值

以上对于“2020 年代的某个时间”或“21 世纪的某个时间”等近似值相当有效，但有时您的意思是“两侧一个月”或“几天左右”。只需将这些作为生成 UDF 的附加选项添加，一切都会好起来的。如果需要，请扩大精度场大小。

性能

从性能上讲，每次计算范围并不一定是理想的。您最好将该范围存储在某个位置，要么作为额外字段存储在表中，要么存储在以日期和精度为键的单独缓存表中，然后在查询期间将其联接：

CREATE TABLE ApproximateDateRanges 
(
    [Date] DATE NOT NULL,
    [Precision] TINYINT NOT NULL,
    RangeStart DATE NOT NULL,
    RangeEnd DATE NOT NULL
    
    PRIMARY KEY (Date, Precision)
);

CREATE TABLE Sample
(
    ID INT IDENTITY PRIMARY KEY,
    ApproxDate DATE NOT NULL,
    ApproxPrec TINYINT NOT NULL
);

-- Some sample data

INSERT INTO Sample(ApproxDate, ApproxPrec)
VALUES
    ('2023-10-25', 4),
    ('2023-10-25', 5)

-- Update ApproximateDateRanges after modifying rows in `Sample`
MERGE INTO ApproximateDateRanges t
USING (SELECT DISTINCT ApproxDate [Date], ApproxPrec [Precision] FROM Sample) s
ON t.[Date] = s.[Date] AND t.[Precision] = s.[Precision]

WHEN NOT MATCHED BY TARGET THEN 
    INSERT([Date], [Precision], RangeStart, RangeEnd)
    VALUES(s.[Date], s.[Precision], dbo.ApproxDateLowerBound(s.[Date], s.[Precision]), dbo.ApproxDateUpperBound(s.[Date], s.[Precision]))

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- Query

DECLARE @targetDate DATE = '2023-12-01';

SELECT s.*
FROM Sample s
JOIN ApproximateDateRanges r ON s.ApproxDate = r.[Date] AND s.ApproxPrec = r.[Precision]
WHERE @targetDate BETWEEN r.RangeStart AND r.RangeEnd;

这在查询速度（比在查询中调用 UDF 更快）和存储（小于在记录中存储范围，尤其是在发生大量冲突时）之间取得了很好的平衡。但是，它对错过的表更新更敏感。ApproximateDateRanges

预先计算的范围

在某些情况下，有效日期的整个范围受到限制 - 例如本世纪上半叶或过去 70 年的日期或其他日期 - 然后您可以预先计算所有日期范围并将它们添加到表格中。这样，您就不必担心在每次编辑其他表中的数据后更新表...但是您必须为要覆盖的每个有效年份填写大约 2.6K 的记录。ApproximateDateRanges

可能不适合你。不是每个人都希望存储一个包含 25 万条记录的表，这些记录大部分不会被使用。不过速度很快。（查看数据仓库中使用日期维度的方式和原因。

使用 UNIX 时间戳。

创建两个附加列：

• mid

  • 时间跨度的中点
    • 天2023-01-10 -> 2023-01-10 12:00:00
    • 月 -> - 通过将最小值和最大值附加到缺失的部分，然后将值相加并除以 2 来计算。2000-012000-01-15:xx

• precision

  • mid- 上面开始的分钟。+ 少量（或 2 个）回旋余地

开始时间只是字符串“0000-01-01 00：00”字符串被数字数据覆盖，并通过查找季度进行类似的替换。

结束时间是被数字数据覆盖的字符串“9999-12-31 12：59”，在 s 的情况下，必须删除十年的最后 0。通过查找表替换了 Q。最后一个修正是一个月的天数。

ABS 减去 UNIX 时间戳并比较所需精度的值。

关于将原始字符串值与转换后的值一起存储是一个好主意，因为可以分析整个使用过程中的有效性。

关于带有 postgresql 的 .Net，我建议使用 DateTimeOffset 类型，它将完成直到时区的时间。

关于价值转换，我留下以下想法。

1. 创建一个枚举来对支持的转换类型进行分类。
2. 一种对枚举排序的方法。
3. 一个将枚举作为其键作为其值的字典，一个接收字符串并返回 DateTimeOffset 的函数。
4. 使用时区存储数据

您必须使用三列，前两列是“开始”和“结束”（定义“范围”），第三列仅存储原始数据以进行任何调试。您的应用程序应计算 datediff 并得出有关事件时代的有意义的结论。

DateTimeStart           | DateTimeEnd          | OriginalString     (Meaning)
2023-01-01 12:00:00     | 2023-01-01 12:00:00  | 2023-01-01 12:00:00 (Exact time known)
2010-01-10 00:00:00     | 2010-01-10 23:59:59  | 2010-01-10 (the timespan is for one day, which means that we know the date, but not the exact time)
2000-01-01 00:00:00     | 2023-01-31 23:59:59  | 2000-01 (time span is one month, so somewhere in January 2010)
2000-01-01 00:00:00     | 2000-03-31 23:59:59  | 2000-Q1 (time span is three months, so a quarter)
1995-01-01 00:00:00     | 1995-12-31 23:59:59  | 1995  (time span is a year, so event happened in that year)
1990-01-01 00:00:00     | 1999-12-31 23:59:59  | 1990s (time span is a decade, so event happened in that decade)

n 列日期复合，例如： 如果计时精度为日： YEAR NOT NULL、MONTH NULL、DAY NULL，最后一列 DURATION（默认值为 1）和约束为： 检查（月份不为空，日为空或月不为空，日不为空）

我们不想要没有一个月的一天，而不是空！

例子：

2010 1 10 + DURATION 1
2000 1 NULL + DURATION 31
2000 1 NULL+ DURATION 91
1995 NULL NULL + DURATION 365
1990 NULL NULL + DURATION 3653

这是我用来从中年到实际日期的历史事实

当然，你可以将原理扩展到 H M S

要重建完整的周期，只需使用：

BETWEEN DATEFROMPARTS(YEAR, COALESECE(MONTH, 1), COALESCE(DAY, 1)) AND
        DATEAD(DAY, DURATION, DATEFROMPARTS(YEAR, COALESECE(MONTH, 1) COALESCE(DAY, 1))

使用 DateTime 范围

它可以表示从特定时间点到十年的任何时期。 它很容易允许查询与给定范围重叠的周期，这可以使用 SQL 的 BETWEEN 关键字或范围运算符来完成。 它简化了用于确定搜索筛选器是否与存储的日期范围重叠的逻辑。 然而，这种方法的主要挑战实际上是为了显示目的而转换回原始字符串表示形式，并维护两个事实来源：字符串和范围。要处理此问题，您可以：

将原始字符串与计算范围一起存储，确保始终可以向用户显示人类可读的初始格式。 在需要时，使用函数或视图将范围转换回人类可读的格式，以便进行显示。

替代：精度指示器另一种方法是使用组合字段策略，其中存储：

可用的最精确的日期时间。 指定日期时间精度的精度指示器（例如，“年”、“月”、“日期”、“时间”）。 此方法在 SQL Server 架构中可能如下所示：

CREATE TABLE ProductReleaseDates (
  ReleaseId INT PRIMARY KEY,
  ReleaseDateTime DATETIME2,
  ReleasePrecision VARCHAR(10) -- 'year', 'month', 'date', 'time', 'decade'
);

在 C# 中，你可以用如下的类来表示这一点：

public enum DatePrecision { Year, Month, Date, Time, Decade }

public class ProductReleaseDate {
  public int ReleaseId { get; set; }
  public DateTime ReleaseDateTime { get; set; }
  public DatePrecision ReleasePrecision { get; set; }

  // Method to convert back to string or perform other operations
}

这使您可以：

以最高的精度将日期时间存储在单个字段中。 使用日期时间值和精度指示器轻松查询基于已知精度。 使用应用程序逻辑中的精度指示器转换回原始格式。

在格式之间转换若要在基于范围的方法和字符串表示形式之间进行转换，可以在应用程序中维护了解如何序列化和反序列化这些值的逻辑。数据库可以保持与显示格式无关，而应用程序包含向用户准确表示数据所需的逻辑。

交集的 SQL 查询要计算 SQL 中范围之间的交集，可以使用 OVERLAPS 关键字或 WHERE 子句中的手动检查来查找重叠的周期。