Storing a TimeSpan between two DateTimes

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?

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.

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).
• 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.

• 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

  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

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

  A Full Day on 24th May: 2011-05-24 00:00:00.000 => 2011-05-25 00: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?

Edit: also

Does my representation of end date make sense? So that 2nd of may wi开发者_运维技巧ll 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..

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).

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.

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

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

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

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.

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

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.