存储星期几和时间? [英] Store the day of the week and time?

问题描述

关于在数据库中存储星期几和时间，我有一个由两部分组成的问题.我使用的是 Rails 4.0、Ruby 2.0.0 和 Postgres.

I have a two-part question about storing days of the week and time in a database. I'm using Rails 4.0, Ruby 2.0.0, and Postgres.

我有某些活动，而这些活动有时间表.例如，对于跳伞"活动，我可能有周二和周三以及下午 3 点.

I have certain events, and those events have a schedule. For the event "Skydiving", for example, I might have Tuesday and Wednesday and 3 pm.

1. 有没有办法让我将周二和周三的记录排成一行，或者我应该有两条记录?
2. 存储日期和时间的最佳方式是什么?有没有办法存储星期几和时间(不是日期时间)或者这些应该是单独的列吗?如果它们应该分开，我将如何存储星期几?我正在考虑将它们存储为整数值，星期日为 0，星期一为 1，因为 Time 类的 wday 方法就是这样做的.

1. Is there a way for me to store the record for Tuesday and Wednesday in one row or should I have two records?
2. What is the best way to store the day and time? Is there a way to store day of week and time (not datetime) or should these be separate columns? If they should be separate, how would I store the day of the week? I was thinking of storing them as integer values, 0 for Sunday, 1 for Monday, since that's how the wday method for the Time class does it.

任何建议都会非常有帮助.

Any suggestions would be super helpful.

推荐答案

有没有办法让我存储周二和星期三连续还是应该有两条记录?

Is there a way for me to store the the record for Tuesday and Wednesday in one row or do should I have two records?

有多种方法可以在一行中存储多个时间范围.@bma 已经提供了其中的几个.这对于使用非常简单的时间模式节省磁盘空间可能很有用.干净、灵活和标准化"方法是每个时间范围存储一行.

There are several ways to store multiple time ranges in a single row. @bma already provided a couple of them. That might be useful to save disk space with very simple time patterns. The clean, flexible and "normalized" approach is to store one row per time range.

存储日期和时间的最佳方式是什么?

What is the best way to store the day and time?

使用timestamp(如果可能涉及多个时区，则使用timestamptz).选择一个任意分期"周 并在使用 timestamp 的日期和时间方面时忽略日期部分.根据我的经验，这是最简单和最快的，并且所有与日期和时间相关的健全性检查都是自动内置的.我在几个类似的应用程序中使用以 1996-01-01 00:00 开头的范围，原因有两个:

Use a timestamp (or timestamptz if multiple time zones may be involved). Pick an arbitrary "staging" week and just ignore the date part while using the day and time aspect of the timestamp. Simplest and fastest in my experience, and all date and time related sanity-checks are built-in automatically. I use a range starting with 1996-01-01 00:00 for several similar applications for two reasons:

• 一周的前 7 天与该月的某一天重合(对于 sun = 7).
• 同时是最近的闰年(为年度模式提供 2 月 29 日).

由于您实际上是在处理时间范围(不仅仅是日期和时间")，我建议使用 内置范围类型tsrange(或tstzrange).一个主要优势:您可以使用内置的范围函数库和运算符.需要 Postgres 9.2 或更高版本.

Since you are actually dealing with time ranges (not just "day and time") I suggest to use the built-in range type tsrange (or tstzrange). A major advantage: you can use the arsenal of built-in Range Functions and Operators. Requires Postgres 9.2 or later.

例如，您可以在此基础上建立一个排除约束(通过可能提供额外好处的全功能 GiST 索引在内部实施)，以排除重叠的时间范围.有关详细信息，请考虑此相关答案:

For instance, you can have an exclusion constraint building on that (implemented internally by way of a fully functional GiST index that may provide additional benefit), to rule out overlapping time ranges. Consider this related answer for details:

• 在 PostgreSQL 中使用 EXCLUDE 防止相邻/重叠条目

对于这个特定的排除约束(没有重叠范围每个事件)，您需要在约束中包含整数列event_id，因此您需要安装附加模块btree_gist.每个数据库安装一次:

For this particular exclusion constraint (no overlapping ranges per event), you need to include the integer column event_id in the constraint, so you need to install the additional module btree_gist. Install once per database with:

CREATE EXTENSION btree_gist;  -- once per db

或者您可以通过一个简单的CHECK 约束来使用范围包含"来限制允许的时间段运算符 <@.

Or you can have one simple CHECK constraint to restrict the allowed time period using the "range is contained by" operator <@.

可能看起来像这样:

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 天、周一至周日或任何适合您的时间.以类似的方式按月或按年安排.

For a weekly schedule use the first seven days, Mon-Sun, or whatever suits you. Monthly or yearly schedules in a similar fashion.

@CDub 提供了一个模块来在 Ruby 端处理它.我无法对此发表评论，但您也可以在 Postgres 中做任何事情，而且性能无可挑剔.

@CDub provided a module to deal with it on the Ruby end. I can't comment on that, but you can do everything in Postgres as well, with impeccable performance.

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)

或者以类似的方式用于范围类型:

Or in similar fashion for range types:

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 这里
_旧sqliddle

ISODOW 代替 DOW 用于 EXTRACT() 在星期日返回 7 而不是 0.有一长串您可以提取的内容.

ISODOW instead of DOW for EXTRACT() returns 7 instead of 0 for sundays. There is a long list of what you can extract.

此相关答案演示了如何使用范围类型运算符来计算时间范围的总持续时间(最后一章):

This related answer demonstrates how to use range type operator to compute a total duration for time ranges (last chapter):

• 计算 PostgreSQL 中 2 个日期之间的工作时间

这篇关于存储星期几和时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！