storing durations (timespans) in sql server

I have to store a duration in a SQL Server 2008 database. How 开发者_JS百科is this usually done?

For example I will have to be able to store values like: 5 minutes, 8 hours, 10 days, etc.

I was thinking to store it in a smalldatetime field as mindatetime + timespan. Or maybe as an int representing an offset to some minimum date.

Anyone has any experience with this?

If the start time is significant (because your recording an event for example) then you will always want to store it as a datetime so you can read it back later, storing the subsequent duration can simply be another "ended" datetime.

If its an arbitrary duration (movie length etc) I would simply store it as seconds in an integer, then add prettifying code in the presentation layer to display it as hours/days etc.

One word of caution when using the approach with start and end time is the daylight savings time change. You have to make sure that the SQL Server datatype that you're using is supporting the timezone information. Also, you need to make sure, it supports historical DST changing information. For example, before 2007 in US daylight savings time change rules were different than they are currently.

In our databases we just go for safe approach and store start time, end time and duration in seconds.

What I would do is have 2 columns one being the last time an event was fired and the other being how long between fires of the event. That way you could do a query of the dateDiff between the last time fired and now() and see if that value is greater then the duration between fires.

As for the value of the duration I would have it be the minimum value of time that you need. Such that if you have a min value of 5 minutes then the column should be in minutes. if you have a min value in seconds then it should be in seconds.