Postgresql中比较字符串日期的最有效方法

Question

我们有一个在AWS上运行的PostgreSQL数据库(引擎版本11.8(。
对于每个项目，我们都将日期存储为字符串[varchar type](以下格式-"2020-12-16"(
我们的应用程序要求进行日期范围查询。基于此，在PostgreSQL中进行字符串日期比较的最佳和最有效的方法是什么
我在SO上看了几个问题，但没有人谈论将日期存储为类型"是否有区别的问题；varchar"；或类型"；日期">
同样基于上面的两种存储类型，最有效的查询方式是什么？特别是，我们正在查询范围(例如"2020-12-10"one_answers"2020-12-16"(
非常感谢您的反馈

Answer 1

没有人谈论将日期存储为类型"是否存在差异的问题；varchar"；或类型"；日期"；。

修复您的数据模型！您应该使用正确的数据类型来存储数据：日期应该存储为date。使用错误的数据类型是错误的，原因有很多：

• 无论何时需要进行日期运算，都需要将字符串转换为日期(例如：如何将一个月添加到string'2020-12-16'？(；这是一种效率很低的

• 在存储数据时，无法强制执行数据完整性；即使使用检查约束也是不够的。你如何判断'2021-02-29'是否有效？

进行查询的最有效方法是什么？我们特别关注范围查询。

也就是说，您使用的格式可以进行直接的字符串比较。因此，对于一个简单的范围比较，我建议字符串操作：

where mystring >= '2020-12-10' and mystring < '2020-12-16'

Answer 2

在考虑性能之前：使用正确的数据类型。

例如，尝试使用字符串表示在2020中获取所有friday the thirteenth。有了日期，这很容易：

---

CREATE table thisyear(
thedate DATE NOT NULL PRIMARY KEY
);
INSERT INTO thisyear(thedate)
SELECT generate_series('2020-01-01'::date, '2021-01-01'::date -1, '1 day'::interval)
;
SELECT * FROM thisyear
WHERE date_part('dow', thedate) = 5 -- friday
AND date_part('day', thedate) = 13  -- the thirteenth
;

---

结果：

---

CREATE TABLE
INSERT 0 366
thedate   
------------
2020-03-13
2020-11-13
(2 rows)