设计不重叠的日期时间事件 [英] Designing non-overlapping date-time events

问题描述

我有以下问题：

事件有一个开始和结束时间和金额。我使用MySQL DATETIME两者。

现在，如果我有一个约束，说没有重叠的事件，我需要做一些检查等，但如何设计？用户只需要5分钟精度，但是我想用秒计算，因为简单/更清洁

如果我有一个事件A）与开始的YYYY-MM-DD 12:00:00 - YYYY-MM-DD 14:00:00

和另一个

（B）YYYY-MM-DD 14:00:00 - YYYY-MM-DD 16:15:00 - >它们是不重叠的，即使他们都包含14:00:00。

为了确定他们确实是非重叠的，我应该使用

B.begin等。

或

A.end< = B.begin等，并使所有的结束时间都是DATE HH：MM：00（减一秒）而不是DATE HH：MM：00，即A.end将是YYYY-MM-DD 13:59： 59而不是YYYY-MM-DD 14:00:00

第一个是最简单的，但如果我有很多不同的事件，它会错过任何重叠并且需要检查我不是超量预订，即事件个人数量不超过总金额（例如：如果每个事件是预订一些表的人数，我不能超过总数在任何给定时间的表数）

解决方案

在使用日期时间范围时使用范围很常见在开始时是包容性的，在结束时是排他性的。例如：

 （使用ISO8601格式）
 
开始结束
 2013-04- 29T01：00：00Z  -  2013-04-29T02：00：00Z 
 2013-04-29T02：00：00Z  -  2013-04-29T03：00：00Z 
  / pre> 
 
 
当值小于或等于开始时，值大于（但不等于）结束。在上面的例子中， 02:00 属于第二个范围，而不是第一个范围。换句话说：
 开始< = value<结束
  
或者等价地，
 开始< = value AND End>值
  
在数学中，使用间隔符号，这被称为半开间隔。
  [开始，结束] 
  
这总是比使用值如 01:59:59 。考虑我是否要减去 End  -  Start 来获得持续时间。我希望答案是一个小时，而不是59分59秒。
 
 
 
大多数例子使用术语开始/结束，但有时您会看到开始/结束或开始/停止。就个人而言，当您拥有包含/排除范围时，我认为最适合使用的术语是开始/直到。它具有两个术语的附加优势，它们是5个字符，按字母顺序排列，并明确表示结束日期是排他的。
 
 
 
另外，当您在谈论不同的事件时你应该记录你的时间为UTC，以防止在时区周围的混乱。这对于本地应用程序来说甚至是重要的，因为许多时区都经过夏令时转换。您不希望在数据库中记录的值不明确。在MySQL中，您可以使用 TIMESTAMP 数据类型确保值以UTC存储，也可以使用 DATETIME 数据类型，如果您可以确定您在应用程序代码中使用UTC值。
 
I have the following problem:

Events have a "begin" and "end" time and an amount. I use MySQL DATETIME for both.

Now if I have a constraint that says "no overlapping events" I need to make some checks etc. but how to design that? The user only needs 5-mins precision or so, but i want to do calculations with seconds since that is "simpler"/"cleaner"

If I have an event (A) with start-end "YYYY-MM-DD 12:00:00"-"YYYY-MM-DD 14:00:00" 

and another

(B) "YYYY-MM-DD 14:00:00"-"YYYY-MM-DD 16:15:00" -> they are non-overlapping even though they both contain 14:00:00.

In order to determine that they are indeed non-overlapping should I use

A.end < B.begin etc.

Or

A.end <= B.begin etc. AND make a "hack" such that all end-times are "DATE HH:MM:00 (minus one second)" instead of "DATE HH:MM:00" i.e. A.end would then be "YYYY-MM-DD 13:59:59" instead of "YYYY-MM-DD 14:00:00"

The first is simplest, but will it miss any overlaps if I have a lot of different events and need to check that i don't "overbook" i.e. that the events individual amounts don't exceed the total amount (example: if each event is a booking of a table with a number of persons, I can't exceed the total number of tables at any given time)
解决方案
It is very common when working with date-time ranges that you use a range that is inclusive at the start and exclusive at the end.  For example:
(using ISO8601 formatting)

Start                  End
2013-04-29T01:00:00Z - 2013-04-29T02:00:00Z
2013-04-29T02:00:00Z - 2013-04-29T03:00:00Z
A value is in range when it is less than or equal to the start, and greater than (but not equal to) the end.  In the example above, 02:00 belongs to the second range, not the first one. In other words:
Start <= value < End 
Or equivalently,
Start <= value  AND  End > value
In Mathematics, using Interval Notation, this is known as a "half-open" interval.
[Start, End)
This is always a better approach than the idea of using a value like 01:59:59.  Consider if I were to subtract End - Start to get a duration.  I would expect the answer be one hour, not 59 minutes and 59 seconds.

Most examples use the terms Start/End, but sometimes you will see Begin/End or Start/Stop.  Personally, I think the best set of terms to use when you have an inclusive/exclusive range is Start/Until.  It has the added advantage of both terms being 5 characters, lining up alphabetically, and explicitly conveying that the end date is exclusive.

Also, when you are talking about distinct events, you should record your times as UTC to prevent confusion around time zones.  This is even important for local applications, as many time zones go through daylight savings transitions.  You don't want the values you record in the database to be ambiguous.  In MySQL, you can use the TIMESTAMP data type to make sure values are stored as UTC, or you can use the DATETIME data type if you can be sure you are working with UTC values in your application code.

                        
这篇关于设计不重叠的日期时间事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！