SQL Creating a table for song duration-CodePudding

I am trying to create a table in SQL which is about Music, it should contain songDuration. Which means I gotta hold minutes:seconds information in the table. But i have no idea what to use for the type. I am using SQL server.

Edit: I want to use the database for an ASP.NET Core web application. I was using a ready-to-use SQL database like northwnd. Now, I am trying to create one. So, I will not see the timing with SELECT function in SQL query. So, I need to use something that makes it mm:ss otomaticly. Is there is a type that I can decleare like that?

create table musics(
   songDuration type,
   ...)

CodePudding user response：

Why just don't you use int? So you could calculate duration in the way you like. E.g. minutes,hours, etc.

CodePudding user response：

I do this by using an int column, and store the seconds there.
In your client you can calculate from the seconds howmany days, hours, minutes and seconds it is and display it like you want.

To display it in sql you can use this for example

declare @seconds int = 350

select   right('00'   convert(varchar, datepart(hour, dateadd(s, @seconds, 0))   ((@seconds / 86400) * 24)), 2) 
         ':'   right('00'   convert(varchar, datepart(minute, dateadd(s, @seconds, 0))), 2) 
         ':'   right('00'   convert(varchar, datepart(second, dateadd(s, @seconds, 0))), 2)

this will display

00:05:50

which is 0 hours, 5 minutes and 50 seconds

IF the value of seconds is larger than a day, this will be no problem. The number of hours will simply be greater

a value of 350000 will display 97:13:20

CodePudding user response：

There's a datatype time which would be the logical choice, that stores it in format HH:mm:ss with an optional amount of fractional seconds determined by the size you declare the field (e.g. time(3) holds it to three decimal places)

If your source data is already in this notation it makes it getting it in the table easy and simple sorting/filtering operates as you expect. The downside to doing this is if you want to do certain operations such as SUM or AVG (because as a_horse_with_no_name pointed out in their comment) time technically represents a point in time not a duration and you'd have to do some workaround like so:

SELECT totalduration = SUM(DATEDIFF(SECOND, '0:00:00', duration)) 
FROM dbo.table

Alternatively you could store the number of (say) seconds in the duration using an int, but if you don't already have the information in that format you'd have to do some (light) conversion when inserting the data, and then back if you want to display in mm:ss

e.g:

SELECT CONVERT(varchar, DATEADD(SECOND, durationinseconds, 0), 8) FROM dbo.table

which would convert it back to hh:mm:ss format.