如何按日期（年/月/日/我们）存储可用资源在数据库中？ [英] How can I store resource available by date (year/month/day/our) in database?

问题描述

我有一个管理资源（代理）的程序。该程序具有从日历中选择日期的功能（例如：2013年8月11日凌晨5点），并在选定的时间取回可用资源的数量。如何在关系数据库中有效地存储一年中每一小时的资源状态？

I have a program which manage resources (agents). In the program there is a feature to select a date from a calendar (e.g.: 08/11/2013 5 a.m.) and get back the number of available resources at selected time. How can I store the status of the resources for every hour of all day in the year effectively in a relational database?

资源状态只是一个字符串：可访问或

Resource status just a string: "accessible" or "busy".

您对此有任何想法或策略吗？

Have you any idea or strategy for this?

感谢您的帮助！

推荐答案

存储有效日期（/时间）范围的最佳方法是日期（/时间）范围。换句话说，存储具有有效日期/时间和到期日期时间的记录。

The best way to store effective date(/time) ranges is as date(/time) ranges. In other words, store records with an effective date/time and an expiry date time.

不要尝试将时间分成每个记录的大块。这是不灵活的，会极大地增加您的数据维护负载。

Don't try to break times into chunks with records for each. This is inflexible and multiplies your data maintenance load very badly.

因此，对于您的情况，想知道何时有代理可用，则具有类似这样的记录结构：

So for your case, where you want to know when an agent is available, have a record structure something like this:

• Agent_ID


• 有效日期时间


• 有效日期

请注意，您不需要状态（可访问对忙），因为必须在表格中访问代理。假定没有代理在特定时间点有效的记录，那么就不能访问它们。

Note you don't need a status ("accessible" vs "busy") since agents have to be in the table to be accessible. It is presumed that if there is no record for an agent effective at a particular point in time, then they must not be accessible.

如果您想知道哪些代理是在2013年11月11日05:00可用，然后仅查询包含以下内容的WHERE子句： WHERE Effective_Datetime< ='2013-08-11 05:00'和Expiry_Datetime> ='2013-08 -11 05:00'

If you want to know which agents are available at 08/11/2013 05:00 then just query with a WHERE clause that includes: WHERE Effective_Datetime <= '2013-08-11 05:00' and Expiry_Datetime >= '2013-08-11 05:00'

这篇关于如何按日期（年/月/日/我们）存储可用资源在数据库中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！