星期和时间的日子STORE？ [英] Store the day of the week and time?

问题描述

予有关于存储星期和时间的天在数据库中的两部分的问题。我使用Rails 4.0，红宝石2.0.0，和Postgres的。

我有一定的事件，这些事件有一个时间表。对于跳伞事件，例如，我可能有周二和周三下午3点。

1. 有没有办法对我来说，存储的周二和周三的纪录一行还是应该有两条记录？
2. 什么是存储日期和时间的最佳方法是什么？有没有一种方法来存储一周的时间（不是日期时间），或每日应这些是单独的列？如果他们应该是独立的，我怎么会在星期几存储？我想将它们存储为整数值，0代表星期日，1代表星期一，因为那是怎样的 wday 方法Time类做的。

任何建议将是超级有帮助的。

解决方案

  

有没有办法对我来说，存储记录的周二和   周三在一排或者说我应该有两个记录？

有几种方法可以存储多个时间段中的单个行。 @bma已经提供了他们夫妇。这可能是有用的，以节省磁盘空间，非常简单的时间模式。干净，灵活和正常化的方法是将每个时间段一行

  

什么是存储日期和时间的最佳方法是什么？

使用一个 时间戳 （或 timestamptz 如果你要处理多个时间区）。选择一个乱升级周，只是在使用时间戳的日期和时间方面忽略日期部分。简单和最快的在我的经验，所有的日期和时间相关的完整性检查是内置的自动。我用一个范围开始 1996-01-01 00:00 几个类似的应用程序有两个原因：

• 在第7天的周与月日相一致（为太阳= 7 ）。
• 这是最近闰年（为每年的模式提供2月29日）在同一时间。

的范围类型

既然你是实际处理时间的范围的（不只是日期和时间），我建议使用的内置范围类型的 tsrange （或 tstzrange ）。主要优点：您可以使用内置的 范围阿森纳函数和操作符 。需要Postgres的 9.2或更高版本

例如，你可以有一个排除约束建筑上（由一个全功能的GiST索引，可能会提供额外的好处的方式在内部实现），以排除重叠的时间范围。考虑细节此相关的答案：

• <一个href="http://stackoverflow.com/questions/19504727/$p$pventing-adjacent-overlapping-entries-with-exclude-in-postgresql/19505869#19505869">$p$pventing与邻近/重叠项PostgreSQL中排除

对于这个特殊的排除限制（没有重叠的范围的每个事件的），你需要包括约束中的整数列 EVENT_ID ，所以你需要安装额外的模块 btree_gist 。每个数据库安装一次：

 创建扩展btree_gist; - 每一次分贝
 

或者你可以有一个简单的 检查约束由一系列包含操作符来限制使用允许的时长＆LT; @

。

看起来是这样的：

  CREATE TABLE事件（EVENT_ID串行PRIMARY KEY，...）;

CREATE TABLE表（
   EVENT_ID整数NOT NULL参考事件（EVENT_ID）
                    ON DELETE CASCADE ON UPDATE CASCADE
 ，t_range tsrange
 ，PRIMARY KEY（EVENT_ID，t_range）
 ，CHECK（t_range＆LT; @'[1996-01-01 00:00，1996年1月9日00:00）'） - 限制期
 ，排除使用要点（EVENT_ID WITH =，t_range与和放大器;＆安培） - 不允许重叠
）;
 

有关每周计划使用的头七天，周一至周日，或任何适合你。每月或每年的时间表以类似的方式

如何提取星期几，时间等？

@CDub提供处理这个在Ruby端的模块。我不能发表评论说，但你可以做的一切在Postgres里为好，无可挑剔的表现。

  SELECT TS ::时间t_time  - 获取时间（几乎无成本）
选择提取（道琼斯指数从TS）AS道琼斯 - 获取星期（非常便宜）
 

或者以类似的方式进行的范围类型：

 选择提取（道琼斯指数从较低（t_range））AS dow_from  - 周下界的日子
     ，提取（道琼斯指数从上（t_range））AS dow_to  - 同样为上
     ，低（t_range）::时间time_from  - 启动时间
     ，上（t_range）::时间time_to  - 结束时间
从时间表;
 

SQL小提琴。

ISODOW 而不是 DOW 的<一个href="http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT"相对=nofollow> 提取物（） 回报 7 而不是 0 周日。还有就是，你可以提取什么一个长长的清单。

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

• <一个href="http://stackoverflow.com/questions/1839319/calculate-working-hours-between-2-dates-in-postgresql/17282639#17282639">Calculate PostgreSQL中
2日期间的工作时间

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.

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. 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?

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?

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:

• The first 7 days of the week coincide with the day of the month (for sun = 7).
• It's the most recent leap year (providing Feb. 29 for yearly patterns) at the same time.

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.

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:

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

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

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

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.

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.

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

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