在两个日期时间之间存储时间间隔

I need a way to store and manipulate a date range using C# and storing the data in a SQL database. Is storing two DateTimes the best way to go about this?

我需要一种使用c#存储和操作日期范围并将数据存储在SQL数据库中的方法。存储两个日期是最好的方法吗?

For example I need to employees to be able to select the duration they spent working on a specific project by selecting the start date and end date using a DatePicker.

例如，我需要员工能够通过使用DatePicker选择开始日期和结束日期来选择他们在特定项目上花费的时间。

I have the following further requirements:

我有以下进一步的要求:

I need to support half days at start and/or end of the duration.
我需要在开始和/或结束时支持半天。
I need to be able to calculate the number of days between the two dates (as a double where 0.5 is half a day).
我需要能够计算出这两个日期之间的天数(双数，0.5是半天)。
I need to be able to calculate the number of business days between the two dates (as a double).
我需要能够计算出这两个日期之间的工作日数(作为双数)。
The time span needs to be displayed on a jquery calendar.
时间跨度需要显示在jquery日历上。
The minimum duration is half a day.

最短的持续时间是半天。

A Date Range of 1/2 Day From 24th May to a full day 27th May: 2011-05-24 12:00:00.000 => 2011-05-28 00:00:00.000

日期范围为1/2天，从5月24日到5月27日全天:2011-05-24 12:00:00.000 => 2011-05-28 00:00.000

A Date Range of Full Day From 24th May to a 1/2 day 27th May: 2011-05-24 00:00:00.000 => 2011-05-27 12:00:00.000

日期范围从5月24日到5月27日1/2天:2011-05-24 00:01 .000 => 2011-05-27 12:00:02 .000

A Half Day on 24th May: 2011-05-24 12:00:00.000 => 2011-05-25 00:00:00.000

5月24日半天时间:2011-05-24 12:00:00 => 2011-05-25 00:00. 0000。

A Full Day on 24th May: 2011-05-24 00:00:00.000 => 2011-05-25 00:00:00.000

5月24日全天:2011-05-24 00:00.000 => 2011-05-25 00:00.000

Does this representation make sense? Should I rather look at storing a DateTime for the StartDate and a TimeSpan taking into account my requirements?

这种表述有意义吗?考虑到我的需求，我是否应该考虑为StartDate和TimeSpan存储一个DateTime ?

Edit: also

编辑:也

Does my representation of end date make sense? So that 2nd of may will be saved as '2011-05-03 00:00:00.000' because that is when the duration ends. Bearing this in mind I'll need to subtract a day from the end date when displaying this in a calendar..

我对结束日期的描述有意义吗?因此，5月2日将被保存为“2011-05-03 00:00 - 0000”，因为这是期限结束的时候。记住这一点，我需要在日历中显示结束日期减去一天。

6 个解决方案

#1

I suggest to save the start and end date to your database. The difference can always be calculated.

我建议将开始日期和结束日期保存到您的数据库中。差异总是可以计算的。

The critical aspect of date ranges is how to handle the boundaries. You can use a mapper for the start/end date to ensure correct time calculations (Inside/Touching):

日期范围的关键方面是如何处理边界。你可以在开始/结束日期使用绘图机，以确保正确的时间计算(内部/触摸):

// ----------------------------------------------------------------------
public void TimePeriodMapperSample()
{
  TimeCalendar timeCalendar = new TimeCalendar();
  CultureInfo ci = CultureInfo.InvariantCulture;

  DateTime start = new DateTime( 2011, 3, 1, 13, 0, 0 );
  DateTime end = new DateTime( 2011, 3, 1, 14, 0, 0 );

  Console.WriteLine( "Original start: {0}",
                     start.ToString( "HH:mm:ss.fffffff", ci ) );
  // > Original start: 13:00:00.0000000
  Console.WriteLine( "Original end: {0}",
                     end.ToString( "HH:mm:ss.fffffff", ci ) );
  // > Original end: 14:00:00.0000000

  Console.WriteLine( "Mapping offset start: {0}", timeCalendar.StartOffset );
  // > Mapping offset start: 00:00:00
  Console.WriteLine( "Mapping offset end: {0}", timeCalendar.EndOffset );
  // > Mapping offset end: -00:00:00.0000001

  Console.WriteLine( "Mapped start: {0}",
    timeCalendar.MapStart( start ).ToString( "HH:mm:ss.fffffff", ci ) );
  // > Mapped start: 13:00:00.0000000
  Console.WriteLine( "Mapped end: {0}",
    timeCalendar.MapEnd( end ).ToString( "HH:mm:ss.fffffff", ci ) );
  // > Mapped end: 13:59:59.9999999
} // TimePeriodMapperSample

Check out the article Time Period Library for .NET (section Calendar Time Periods).

查看. net的文章时间段库(部分日历时间段)。

#2

Ideally, you will need two fields anyway:

理想情况下，你需要两个字段:

Store two date/time separately --OR--
分别存储两个日期/时间
Store one date/time and store the time-elapsed
存储一个日期/时间并存储时间。

Reviewing your requirements, I would go for two separate date/time fields; and calculate the fulldays/halfdays including (adding/subtracting) holidays.

回顾您的要求，我将分别列出两个日期/时间字段;计算包括(加减)假日在内的全日/半天。

Keep a separate configuration table to define/configure the max/minimum duration of the day.

保留一个单独的配置表来定义/配置一天的最大/最小持续时间。

Perform any calculations on the day/time, within the query, or alternatively on the UI, upon user actions - if you desire.

在查询中或UI中，在用户操作(如果您愿意的话)上执行任何计算。

#3

Only store start and end dates. Then to calculate no of days worked:

只存储开始和结束日期。然后计算工作天数:

Round(DateDiff(hour, StartDate, EndDate)/24, 2)

#4

To me it seems like storing 2 datetime's makes the most sense.

在我看来，存储两个datetime(日期-时间)最合适。

#5

It really depends on how you intend to use your data. If your second date will never be used outside of calculating the TimeSpan, you should not store it, and store the TimeSpan instead. If you use the end date frequently and only seldom calculate the duration, you should store the 2 datetimes.

这实际上取决于您打算如何使用您的数据。如果在计算TimeSpan之外永远不会使用第二个日期，那么不应该存储它，而应该存储TimeSpan。如果您经常使用结束日期，并且很少计算持续时间，您应该存储两个日期时间。

You can even consider to store the three values if you use them frequently and the increase in memory space is not a problem.

如果您经常使用这三个值，甚至可以考虑存储它们，并且增加内存空间不是问题。

#6

I believe two DateTime Fields along with checkboxes corresponding to Start and End date to indicate whether its Half or Full day would be sufficient for you. based on the Checkbox state you can manipulate the time component to suit your needs. You will just have to store the Start and End DateTime values which would adhere to the requirements you posted.

我相信两个DateTime字段以及与开始日期和结束日期对应的复选框来指示它的半天或一整天是否足够。根据复选框状态，您可以操作time组件以满足您的需求。您只需存储开始和结束日期时间值，这些值将遵循您发布的需求。

#1