比方说，我保持事件的数据库，这可能是从一个企业的运作时间在每周安排任何东西（周一至周五：10 00-19：00，周六：12 PM-6PM，太阳：关闭）每月事件（艺术博览会，每一个星期六，上午10点 - 下午5点），以每年一度的盛事（圣诞夜慈善晚宴，7 PM-9PM）

Let's say I'm maintaining a database of events, which could be anything from the weekly schedule of a business's operating hours (Monday-Friday: 10AM-7PM, Sat: 12PM-6PM, Sun: Closed) to monthly events (Art fair, every first Saturday, from 10AM - 5PM) to an annual event (Christmas Eve charity dinner, 7PM-9PM)

在理想情况下，我愿与任何一天查询数据库，说： 周四，2012年1月12日，下午12点

In an ideal situation, I would like to query the database with any given day, say: Thursday, Jan. 12, 2012, 12PM

...，发现正在发生的所有事件：

...and find all events that are occurring on:

我想有一个在谈论查询构造不先想着怎么这么日期/时间将被存储在数据库中没有任何意义。

I suppose there's no point in talking about the query construction without first thinking how such dates/times would be stored in a database.

我想不出如何定期每周工作小时（甚至忽略边缘的情况下）的概念，可以在一记录，单场，这也将模拟一个曾经每一年活动进行建模。最起码，好像我至少需要五个领域：

I can't think of how the concept of regular weekly operating hours (even ignoring edge cases) could be modeled in a one-record, single field that would also model a once-every-year event. At the very least, it seems like I would need at least five fields:

和我猜没有办法封装在此单排，​​对不对？例如，一个企业打开每个工作日将有五条记录。

And I'm guessing there's no way to encapsulate this in a single row, right? For example, a business open every weekday would have five records.

和最终目标是能够做一个比较优雅的查询，可以找到所有的事件记录，其中包含一个特定的时刻在他们的时间表。

And the end goal is to be able to do a relatively elegant query that could find all event-records which contain a given moment in their timeframe.

推荐答案

不知道你是专门要求数据库管理系统无关的解决方案，反正在PostgreSQL的（我认为这是可能的，在大多数RDBMS），您可以存储时间戳和获得很多信息了出来： http://www.postgresql.org /docs/8.4/interactive/functions-datetime.html 。

Not sure if you are asking specifically for a dbms-agnostic solution, anyway in postgresql (and I think it's possible in most RDBMS) you can store Timestamps and get a lot of info out of them: http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html.

PostgreSQL特有的答案

9.9.1。提取物，date_part数

9.9.1. EXTRACT, date_part

提取物（场源文件）将提取函数获取子场   比如年或者小时，从日期/时间值。源必须是一个值   timestamp类型，时间或间隔EX pression。 （出$ P $型pssions   日期转换为timestamp，因此可以使用。）场   是一个标识符或字符串，选择什么领域，从萃取   来源值。 extract函数返回double类型的值   precision。以下是有效的字段名：[...]天，DOW（星期），DOY（年日），[...]

EXTRACT(field FROM source) The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. (Expressions of type date are cast to timestamp and can therefore be used as well.) field is an identifier or string that selects what field to extract from the source value. The extract function returns values of type double precision. The following are valid field names: [...] day, DOW (day of week), DOY (day of year), [...]

因此​​，举例来说，选择事件发生的每一个第2星期四，你可以写：

So for instance, to select events occurring each 2nd Thursday you could write:

SELECT * FROM events  #your table
    WHERE EXTRACT(DOW FROM date_column)=4 # DOW  goes from sunday (0) to saturday (6)
    AND EXTRACT(DAY FROM date_column)>7 # must occurr after 7th day of the month (ie. if the 1st is a Thursday
    AND EXTRACT(DAY FROM date_column)<15 # and before the 15th (ie. if the 1st is a Friday, so the first Thursday is on the 7th, and the second is on the 14th)

要模拟的持续时间，你可能只是有2个时间戳字段，该事件的开始/结束。

To model duration you may simply have 2 timestamp fields, for the start/end of the event.

另外请注意，您可以添加或减去时间戳，以及知道，如果时间间隔重叠。

Also note that you can add or subtract timestamps, as well as know if intervals overlap.

一般，我会首先尝试使用由数据库管理系统提供的数据类型和功能，只有当你真的不能找到一个解决方案，尝试自己建模。

In general, I would first try to use datatypes and functions provided by your DBMS, and only if you cannot really find a solution try to model it yourself.