Storing video duration time in sql server.

Question

What's the most appropriate type used to store the duration time information of a video in sql server?

Answer 1

I would store it as an INT representing the number of seconds, but I suppose it all depends what you need to do with the information in your database (you wouldn't want to have to convert it to HH:MM:SS or something else in your DB)

Answer 2

It depends on how granular you need and if you have any constraints on a maximum time. For example, would you need to know down to the millisecond of time duration or is 1 second granular enough? The other thing to consider is how much data do you (or can you) store.

For SQL Server 2005, you have these constraints:

tinyint

• min = 0
• max =255
• Size = 1 byte

smallint

• min = -2^15 (-32,768)
• max= 2^15 - 1 (32,767)
• Size = 2 bytes

int

• min = -2^31 (-2,147,483,648)
• max = 2^31 - 1 (2,147,483,647)
• Size = 4 bytes

bigint

• min = -2^63 (-9,223,372,036,854,775,808)
• Max = 2^63 - 1 (9,223,372,036,854,775,807)
• Size = 8 bytes

Answer 3

There are several options, including using the builtin DateTime or Time data type with offset from a particular fixed zero (which will allow you to use the built-in date/time function to get hours, minutes and seconds, etc.

If you were on pre-SQL Server 2005, you could combine it with a user-defined data type technique (if your spans are less than 24 hours) to constrain the date part to be guaranteed not to wander.

Answer 4

Just store it as a DateTime - you get sorting, formatting and calculations built-in.