How to add SQL Server Time field

Question

How to add SQL Server Time field

Navigation

#1 by (2 votes)
#2 by (0 votes)

3

I am working on a system for a broadcaster, this keeps track of the audio files played on the day, as well as the duration of each audio file. I save this information in a table that has the following structure:

** The description field contains the name of the audio file reproduced, this data can be repeated because during the day a certain song or audio can be repeated several times.

Now I need to group by Description, get the number of times an audio was played, as well as the total minutes or seconds that were played either in a whole value or in the same Time data type. The query that I have armed so far goes like this

SELECT        
OpeRadioReporte.Descripcion,
Count(*) as TotalReproducciones,
SUM(OpeRadioReporte.Duracion) -- ESTO ME GENERA ERROR PUES NO ME SUMA EL CAMPO TIME.
FROM            
OpeRadioReporte
WHERE
MONTH(OpeRadioReporte.Fecha) = 9
AND
YEAR(OpeRadioReporte.Fecha) = 2018
GROUP BY
OpeRadioReporte.Descripcion

Where when trying to add the Time Type field, I get an error.

sql

asked by Alfredo Manuel Can Reséndiz 04.10.2018 в 21:17

source

2 answers

0

Friend, I have a query that can help you a bit to do something similar, I do not know if it serves you. You need to add minutes, at least I have that, because with seconds it's complicated.

SELECT
     [Descripcion],
     SUM(CONVERT(INT, SUBSTRING(REPLACE(CONVERT(VARCHAR(8), [Duracion], 108),':',''), 3,2))) as [Entero] 
FROM OpeRadioReporte  group by Descripcion

I hope you can do tests and tell me

Greetings

answered by 04.10.2018 в 22:31

Algorithm for adding daughter account values in parent accounts in C # .NET How to convert query from sql to linq?

score 2 · Accepted Answer

To add a column TIME you must convert the same to a numeric value, as I imagine that the level of precision of your requirement are the seconds you have to take the TIME to seconds. One way is to decompose the data in hours, minutes and seconds with DATEPART() and multiply each part by the number of seconds. Another interesting way is to convert the TIME to a DATETIME and get the difference with a 0 AS DATETIME .

DECLARE @Ejemplo TABLE (
    Duracion    TIME
)

-- Son dos Registros que suman justo 4 horas    
INSERT INTO @Ejemplo (Duracion) VALUES ('01:12:15'), ('02:47:45')

SELECT  SUM(
        DATEPART(SECOND, [Duracion]) + 
                60 * DATEPART(MINUTE, [Duracion]) + 
                3600 * DATEPART(HOUR, [Duracion] ) 
        ),
    SUM(
        DATEDIFF(SECOND,0,cast(Duracion AS DATETIME))
    )
    FROM @Ejemplo


-- Resultado en segundos
14400

The final result will be seconds, if you want to visualize them again as a TIME you can convert them in the following way:

SELECT CAST(DATEADD(SECOND, 14400, 0) AS TIME)