storing durations (timespans) in sql server

Question

I have to store a duration in a SQL Server 2008 database. How 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?

Answer 1

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.

Answer 2

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.