是否可以以与数据库无关的方式搜索字符串形式的日期？

Question

我有一个带有 PostgreSQL 数据库的 Ruby on Rails 应用程序；一些表具有created_at和updated_at时间戳属性。显示时，这些日期会按照用户的区域设置进行格式化；例如，时间戳 2009-10-15 16:30:00.435 变为字符串 15.10.2009 - 16:30 （本示例的日期格式为 dd.mm.yyyy - hh.mm)。

要求是用户必须能够按日期搜索记录，就好像它们是在当前区域设置中格式化的字符串一样。例如，搜索 15.10.2009 将返回日期为 2009 年 10 月 15 日的记录，搜索 15.10 将返回日期为任意年份 10 月 15 日的记录，搜索 < code>15 将返回与 15 匹配的所有日期（无论是日、月还是年）。由于用户可以使用日期的任何部分作为搜索词，因此无法将其转换为日期/时间戳进行比较。

一种（慢）方法是检索所有记录，格式化日期，然后对其执行搜索。可以通过首先仅检索 id 和日期，执行搜索，然后获取匹配记录的数据来加快速度；但对于大量行来说，它仍然可能很慢。

另一种（与数据库无关的）方法是使用 PostgreSQL 函数或运算符将日期转换/格式化为数据库中的正确格式，并让数据库进行匹配（使用 PostgreSQL 正则表达式运算符或其他）。

有没有一种方法可以以与数据库无关的方式有效地完成此操作（无需获取所有行）？或者您认为我走错了方向，应该以不同的方式处理问题？

Answer 1

根据卡洛斯的答案，如果您在所有日期和日期部分字段上都有索引，那么这应该允许您进行所有搜索而无需全表扫描。基于函数的索引对于日期部分列会更好，但我没有使用它们，因为这不应该是特定于数据库的。

CREATE TABLE mytable (
    col1 varchar(10),
    -- ...
    inserted_at timestamp,
    updated_at timestamp);

INSERT INTO mytable
VALUES
    ('a', '2010-01-02', NULL),
    ('b', '2009-01-02', '2010-01-03'),
    ('c', '2009-11-12', NULL),
    ('d', '2008-03-31', '2009-04-18');

ALTER TABLE mytable
    ADD inserted_at_month integer,
    ADD inserted_at_day integer,
    ADD updated_at_month integer,
    ADD updated_at_day integer;

-- you will have to find your own way to maintain these values...
UPDATE mytable
SET
    inserted_at_month = date_part('month', inserted_at),
    inserted_at_day = date_part('day', inserted_at),
    updated_at_month = date_part('month', updated_at),
    updated_at_day = date_part('day', updated_at);

如果用户仅输入年份，请使用 WHERE Date BETWEEN 'YYYY-01-01' AND 'YYYY-12-31'

SELECT *
FROM mytable
WHERE
    inserted_at BETWEEN '2010-01-01' AND '2010-12-31'
    OR updated_at BETWEEN '2010-01-01' AND '2010-12-31';

如果用户输入年份和月份，请使用 WHERE Date BETWEEN 'YYYY-MM-01' AND 'YYYY-MM-31 ' （可能需要针对 30/29/28 进行调整）

SELECT *
FROM mytable
WHERE
    inserted_at BETWEEN '2010-01-01' AND '2010-01-31'
    OR updated_at BETWEEN '2010-01-01' AND '2010-01-31';

如果用户输入三个值，请使用 SELECT .... WHERE Date = 'YYYY-MM-DD'

SELECT *
FROM mytable
WHERE
    inserted_at = '2009-11-12'
    OR updated_at = '2009-11-12';

如果用户输入月份和日期

SELECT *
FROM mytable
WHERE
    inserted_at_month = 3
    OR inserted_at_day = 31
    OR updated_at_month = 3
    OR updated_at_day = 31;

如果用户输入月份或日期 (您可以优化为每月不检查值 > 12）

SELECT *
FROM mytable
WHERE
    inserted_at_month = 12
    OR inserted_at_day = 12
    OR updated_at_month = 12
    OR updated_at_day = 12;

Answer 2

“与数据库无关的方式”通常是“慢速方式”的同义词，因此解决方案不太可能有效。

无论如何，在客户端解析所有记录都是效率最低的解决方案。

您可以在客户端处理您的区域设置字符串，并为 LIKE、RLIKE 或 REGEXP_SUBSRT 运算符形成正确的条件。客户端当然应该了解系统使用的数据库。

然后，您应该将运算符应用于根据区域设置使用特定于数据库的格式化函数形成的字符串，如下所示（在 Oracle 中）：

SELECT  *
FROM    mytable
WHERE   TO_CHAR(mydate, 'dd.mm.yyyy - hh24.mi') LIKE '15\.10'

更有效的方法（仅在 PostgreSQL 中有效 ） >，但是）将在各个日期部分上创建一个 GIN 索引：

CREATE INDEX ix_dates_parts
ON      dates
USING   GIN
        (
        (ARRAY
        [
        DATE_PART('year', date)::INTEGER,
        DATE_PART('month', date)::INTEGER,
        DATE_PART('day', date)::INTEGER,
        DATE_PART('hour', date)::INTEGER,
        DATE_PART('minute', date)::INTEGER,
        DATE_PART('second', date)::INTEGER
        ]
        )
        )

并在查询中使用它：

SELECT  *
FROM    dates
WHERE   ARRAY[11, 19, 2010] <@ (ARRAY
        [
        DATE_PART('year', date)::INTEGER,
        DATE_PART('month', date)::INTEGER,
        DATE_PART('day', date)::INTEGER,
        DATE_PART('hour', date)::INTEGER,
        DATE_PART('minute', date)::INTEGER,
        DATE_PART('second', date)::INTEGER
        ]
        )
LIMIT 10

这将选择具有所有三个数字（1，2 和 2010）在任何日期部分：例如，2010 年 11 月 19 日 的所有记录加上 19:11 的所有记录> 2010 等

Answer 3

无论用户输入什么，您都应该使用他的区域设置作为指导，提取三个值：Year、Month 和 Day。某些值可能为空。

• 如果用户仅输入年份，请使用WHERE Date BETWEEN 'YYYY-01-01' AND 'YYYY-12-31'
• 如果用户输入年份 > 和 Month 使用 WHERE Date BETWEEN 'YYYY-MM-01' AND 'YYYY-MM-31'（可能需要针对 30/29/28 进行调整）
• 如果用户输入三个值 use SELECT .... WHERE Date = 'YYYY-MM-DD'
• 如果用户输入 Month 和 Day，您必须使用“慢”的方式

Answer 4

恕我直言，简短的回答是否。但绝对要避免加载所有行。

几点注意：

• 如果您只需要简单查询确切的日期或范围，我建议使用 ISO 格式 对于 DATE（YYYY-MM-DD，例如：2010-02-01） 或 DATETIME。但由于您似乎需要诸如“10 月 15 日的所有年份”之类的查询，因此无论如何您都需要自定义查询。
• 我建议您创建一个“解析器”，它接受日期查询并为您提供 SQL WHERE 子句的部分。我确信您最终会遇到不到十几个案例，因此您可以为每个案例找到最佳的 WHERE。这样您就可以避免加载所有记录。
  • 您绝对不想在 SQL 中执行任何特定于语言环境的操作。因此，在非 SQL 代码中将本地转换为某种标准，然后使用它来执行查询（基本上是单独的本地化/全球化和查询执行）
  • 然后您就可以进行优化。如果您发现仅针对year有大量查询，您可以创建一个COMPUTED COLUMN，其中仅包含YEAR并具有索引就在上面。