储存时间和星期。

Challenge : I have a requirement in which I have to implement recurring events. I am storing day of the week , time and the date range for which the event will reoccur.

挑战:我有一个要求，我必须实现循环事件。我正在存储事件将重新发生的星期、时间和日期范围。

Possible solution: Storing time and day of week as string and enumeration. Storing current and setting the time and day for that day ?

可能的解决方案:将时间和星期作为字符串和枚举存储。储存电流，设定当天的时间和时间?

Question : What is the best practice on storing time and day of week ?

问:储存时间和星期的最佳实践是什么?

EDT: I am using SQL Server Database

EDT:我正在使用SQL Server数据库

2 个解决方案

#1

This best approach might dependson the database you are using. But, there are two general approaches, both quite reasonable.

这种最佳方法可能依赖于您正在使用的数据库。但是，有两种通用的方法，都很合理。

The first is to store dates and times in the native format for the database. Most databases have functions to extract day of the week and time from a date time type. You would write your queries using these functions. Typically, you would store these as one field, but sometimes you might want to separate the date and time portions.

第一种方法是以数据库的本机格式存储日期和时间。大多数数据库都具有从日期时间类型中提取星期和时间的功能。您可以使用这些函数编写查询。通常，您会将它们存储为一个字段，但有时您可能希望将日期和时间部分分开。

The second is to have a calendar table. A calendar table would have a date or dateid as a key, and then contain columns for what you want to know about it. Day of the week would be an obvious column.

第二个是有一个日历表。日历表将日期或dateid作为键，然后包含您想要了解的列。一周的一天将是一个明显的专栏。

A calendar table is the preferred solution in some situations. For instance, if you are going to internationalize your application, then being able to get day of the week from a table makes it easier to get the string English, Greek, Chinese, Russian, Swahili, or whatever your favorite language is. Or, if you want to keep track of specific holidays, then a calendar table can store this information as well. Or, if you have a business running on a strange financial calendar (such as 5-4-4), then a calendar table is a good choice.

在某些情况下，日历表是首选的解决方案。例如，如果您要国际化您的应用程序，那么能够从表中获得一周中的一天，就可以更容易地获得字符串英语、希腊语、汉语、俄语、斯瓦希里语，或任何您喜欢的语言。或者，如果您想跟踪特定的假日，那么日历表也可以存储这些信息。或者，如果您的企业运行在一个奇怪的财务日历上(比如5-4-4)，那么日历表是一个不错的选择。

In either case, you do not need to store redundant date information in the table. You should derive it, either from a built-in function or by looking up what you want in a reference table.

无论哪种情况，都不需要在表中存储冗余的日期信息。您应该从内置函数或通过查找您想要的引用表来派生它。

#2

Another alternative is to have computed columns representing the parts that you're after.

另一种选择是用计算列表示您要查找的部分。

CREATE TABLE dbo.foo
(
  bar DATETIME,
  bar_day_of_week AS DATEPART(WEEKDAY, bar),
  bar_time AS CONVERT(TIME, bar)
);

INSERT dbo.foo(bar) SELECT GETDATE();

SELECT bar, bar_day_of_week, bar_time FROM dbo.foo;

#1