有没有办法让我存储星期二和
星期三连续还是我应该有两条记录？
有多种方法可以在一行中存储多个时间范围。 @bma 已经提供了其中几个。这可能有助于通过非常简单的时间模式节省磁盘空间。干净、灵活和“标准化”的方法是存储每个时间范围一行.
存储日期和时间的最佳方式是什么？
Use a timestamp (or timestamptz如果可能涉及多个时区）。选择一个任意“分期”周并在使用日期和时间方面时忽略日期部分timestamp。根据我的经验，这是最简单、最快的，并且所有与日期和时间相关的健全性检查都是自动内置的。我使用的范围以1996-01-01 00:00对于几个类似的应用程序，有两个原因：
- 一周的前 7 天与该月的某一天一致（对于
sun = 7).
- 同时也是最近的闰年（每年模式为 2 月 29 日）。
量程类型
因为你实际上是在和时间打交道ranges（不仅仅是“日期和时间”）我建议使用内置范围类型tsrange https://www.postgresql.org/docs/current/rangetypes.html (or tstzrange）。一个主要优点：您可以使用内置的军火库范围函数和运算符 https://www.postgresql.org/docs/current/functions-range.html。需要 Postgres9.2 或更高版本.
例如，您可以有一个排除约束在此基础上（通过功能齐全的 GiST 索引在内部实现，可能会提供额外的好处），以排除重叠的时间范围。请考虑此相关答案以了解详细信息：
- 在 PostgreSQL 中使用 EXCLUDE 防止相邻/重叠条目 https://stackoverflow.com/questions/19504727/preventing-adjacent-overlapping-entries-with-exclude-in-postgresql/19505869#19505869
对于这个特定的排除约束（没有重叠范围每个事件)，您需要包含整数列event_id在约束中，所以你需要安装附加模块btree_gist https://www.postgresql.org/docs/current/btree-gist.html。每个数据库安装一次：
CREATE EXTENSION btree_gist; -- once per db
或者你可以有一个简单的CHECK约束使用“范围包含于”运算符来限制允许的时间段<@.
可能看起来像这样：
CREATE TABLE event (event_id serial PRIMARY KEY, ...);
CREATE TABLE schedule (
event_id integer NOT NULL REFERENCES event(event_id)
ON DELETE CASCADE ON UPDATE CASCADE
, t_range tsrange
, PRIMARY KEY (event_id, t_range)
, CHECK (t_range <@ '[1996-01-01 00:00, 1996-01-09 00:00)') -- restrict period
, EXCLUDE USING gist (event_id WITH =, t_range WITH &&) -- disallow overlap
);
对于每周计划，请使用前 7 天、周一至周日或任何适合您的时间。以类似的方式制定每月或每年的时间表。
如何提取星期几、时间等？
@CDub 提供了一个模块 https://stackoverflow.com/a/19895268/939860在 Ruby 端处理它。我无法对此发表评论，但您也可以在 Postgres 中完成所有操作，并且性能无可挑剔。
SELECT ts::time AS t_time -- get the time (practically no cost)
SELECT EXTRACT(DOW FROM ts) AS dow -- get day of week (very cheap)
或者对于范围类型以类似的方式：
SELECT EXTRACT(DOW FROM lower(t_range)) AS dow_from -- day of week lower bound
, EXTRACT(DOW FROM upper(t_range)) AS dow_to -- same for upper
, lower(t_range)::time AS time_from -- start time
, upper(t_range)::time AS time_to -- end time
FROM schedule;
db<>fiddle
Old sqliddle http://sqlfiddle.com/#!17/47d8b/2
ISODOW代替DOW for EXTRACT() https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT回报7代替0周日。您可以提取一长串内容。
此相关答案演示了如何使用范围类型运算符来计算时间范围的总持续时间（最后一章）：
- 计算 PostgreSQL 中 2 个日期之间的工作时间 https://stackoverflow.com/questions/1839319/calculate-working-hours-between-2-dates-in-postgresql/17282639#17282639
