存储星期几和时间？

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.

我有一个关于在数据库中存储星期和时间的两部分问题。我正在使用Rails 4.0,Ruby 2.0.0和Postgres。

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.

我有某些事件,这些事件都有一个时间表。例如,对于“Skydiving”活动,我可能有周二,周三和下午3点。

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.

存储日期和时间的最佳方式是什么?有没有办法存储星期和时间(不是日期时间)或者这些应该是单独的列?如果它们应该分开,我将如何存储一周中的哪一天?我想将它们存储为整数值,0表示星期日,1表示星期一,因为这就是Time类的wday方法。

Any suggestions would be super helpful.

任何建议都会非常有用。

4 个解决方案

#1

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?

有没有办法让我将星期二和星期三的记录存储在一行中,或者我应该有两条记录吗?

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.

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

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

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

Use a timestamp (or timestamptz if you have to deal with multiple time zones). 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:

使用时间戳(如果必须处理多个时区,则使用timestamptz)。选择任意“分段”周,并在使用时间戳的日期和时间方面时忽略日期部分。根据我的经验,最简单,最快速,所有与日期和时间相关的完整性检查都是自动内置的。我使用从1996-01-01 00:00开始的范围用于几个类似的应用程序,原因有两个:

• The first 7 days of the week coincide with the day of the month (for sun = 7).

一周的前7天与该月的日期一致(太阳= 7)。

• It's the most recent leap year (providing Feb. 29 for yearly patterns) at the same time.

这是最近的闰年(同时提供2月29日的年度模式)。

Range type

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.

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

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:

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

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

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

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:

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

CREATE EXTENSION btree_gist;  -- once per db

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

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

Could look like this:

看起来像这样:

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
);

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

对于每周时间表,使用前七天,周一至周日或任何适合您的方式。每月或每年的时间表以类似的方式。

How to extract day of week, time, etc?

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

@CDub提供了一个模块来处理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)

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;

SQL Fiddle.

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

对于星期日,ISODOW而不是DOW for EXTRACT()返回7而不是0。您可以提取的内容很长。

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

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

• Calculate working hours between 2 dates in PostgreSQL

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

#2

Check out the ice_cube gem (link).

看看ice_cube gem(链接)。

It can create a schedule object for you which you can persist to your database. You need not create two separate records. For the second part, you can create schedule based on any rule and you need not worry on how that will be saved in the database. You can use the methods provided by the gem to get whatever information you want from the persisted schedule object.

它可以为您创建一个可以持久保存到数据库的计划对象。您无需创建两个单独的记录。对于第二部分,您可以根据任何规则创建计划,您无需担心如何将其保存在数据库中。您可以使用gem提供的方法从持久化的计划对象中获取所需的任何信息。

#3

Depending how complex your scheduling needs are, you might want to have a look at RFC 5545, the iCalendar scheduling data format, for ideas on how to store the data.

根据您的调度需求的复杂程度,您可能需要查看RFC 5545(iCalendar调度数据格式),以获取有关如何存储数据的建议。

If you needs are pretty simple, than that is probably overkill. Postgresql has many functions to convert date and time to whatever format you need.

如果你需要非常简单,那可能是过度的。 Postgresql有许多功能可以将日期和时间转换为您需要的任何格式。

For a simple way to store relative dates and times, you could store the day of week as an integer as you suggested, and the time as a TIME datatype. If you can have multiple days of the week that are valid, you might want to use an ARRAY.

对于存储相对日期和时间的简单方法,您可以将星期几存储为建议的整数,将时间存储为TIME数据类型。如果您可以在一周中有多天有效,则可能需要使用ARRAY。

Eg.

• ARRAY[2,3]::INTEGER[] = Tues, Wed as Day of Week

ARRAY [2,3] :: INTEGER [] =星期二,星期三作为星期几

• '15:00:00'::TIME = 3pm

'15:00:00'::时间=下午3点

[EDIT: Add some simple examples]

[编辑:添加一些简单的例子]

/* Custom the time and timetz range types */
CREATE TYPE timerange AS RANGE (subtype = time);

--drop table if exists schedule;
create table schedule (
    event_id    integer not null, /* should be an FK to "events" table */
    day_of_week integer[],
    time_of_day time,
    time_range  timerange,
    recurring   text CHECK (recurring IN ('DAILY','WEEKLY','MONTHLY','YEARLY'))
);

insert into schedule (event_id, day_of_week, time_of_day, time_range, recurring)
values
(1, ARRAY[1,2,3,4,5]::INTEGER[], '15:00:00'::TIME, NULL, 'WEEKLY'),
(2, ARRAY[6,0]::INTEGER[], NULL, '(08:00:00,17:00:00]'::timerange, 'WEEKLY');

select * from schedule;

 event_id | day_of_week | time_of_day |     time_range      | recurring 
----------+-------------+-------------+---------------------+-----------
        1 | {1,2,3,4,5} | 15:00:00    |                     | WEEKLY
        2 | {6,0}       |             | (08:00:00,17:00:00] | WEEKLY

The first entry could be read as: the event is valid at 3pm Mon - Fri, with this schedule occurring every week.
The second entry could be read as: the event is valid Saturday and Sunday between 8am and 5pm, occurring every week.

第一个条目可以理解为:该活动在周一至周五下午3点有效,此时间表每周发生一次。第二个条目可以理解为:该活动周六和周日上午8点至下午5点有效,每周发生一次。

The custom range type "timerange" is used to denote the lower and upper boundaries of your time range.
The '(' means "inclusive", and the trailing ']' means "exclusive", or in other words "greater than or equal to 8am and less than 5pm".

自定义范围类型“时间范围”用于表示时间范围的下边界和上边界。 '('表示“包含”,尾随']表示“独占”,或换句话说“大于或等于8am且小于5pm”。

#4

Why not just store the datestamp then use the built in functionality for Date to get the day of the week?

为什么不只是存储日期戳,然后使用Date的内置功能来获取星期几?

2.0.0p247 :139 > Date.today
 => Sun, 10 Nov 2013 
2.0.0p247 :140 > Date.today.strftime("%A")
 => "Sunday" 

strftime sounds like it can do everything for you. Here are the specific docs for it.

strftime听起来像是可以为你做的一切。以下是它的具体文档。

Specifically for what you're talking about, it sounds like you'd need an Event table that has_many :schedules, where a Schedule would have a start_date timestamp...

特别是对于你所说的,听起来你需要一个事件表has_many:schedule,其中一个Schedule将有一个start_date时间戳......