How to convert 39 hours to 'TIME' type

Question

To accomplish a certain task, I count in my system the time spent of the respective task.

Today I came across the following:

Data Inicial: 17/12/2018 16:49:14
Data Final: 19/12/2018 08:02:58

totalizando: 2362,29 minutos.

Converto para decimal: (2362,29 / 60) = 39,3716

However I need to convert this value (39,3716) to the format TIME

However, according to documentation time (Transact-SQL)

The permitted range is: 00:00:00.0000000 a 23:59:59.9999999

Would there be some other way to represent that value with the type TIME?

Note: I know that the value 39,3716, represents 39 hours and 50 minutes, but I need the column to be the team TIME, otherwise, could define the column as VARCHAR and present a value of 39:50.

Answer 1

No, the guy TIME was created to indicate a specific point on the time line, not to indicate intervals of time which is what you want. This requirement "I need to convert this value (39,3716) to TIME format" part of a wrong premise, is how to want to measure how much milk has in meters.

The correct way to store a time interval is to place the unit amount of time. Probably the most suitable is the amount of seconds, but can have a higher or lower precision, since you know what it is always, can be only minutes, for example.

The . NET has a more specialized type that deals better with this for you, is the Timespan. Unfortunately the databases don’t usually have a type like this, so you need to control it in hand.

Storing is different from presenting. Many people don’t understand that. You can present it as you like, you can even take that time unit contained in the range and present it as if it were a time. That’s wrong. To say that "the duration is 39 hours and 50 minutes" is correct, to say "the duration is 39:50" is not, this notation is of time and not elapsed time, but if you want you can do it, of course. Has a question that talks about it.

Anyway as the duration fits calculations, the correct is to store it as a number and not as a text. I would go from BIGINT or equivalent in your DBMS to store the seconds elapsed in this interval. For the presentation I would create functions that makes the conversion, something similar can be seen in the link above, it would only be to adapt.

I know, you’ll probably want to insist that the way you’re thinking is fine, but it’s not the right way. People learn wrong and work with error for so long that it is even difficult to understand and assimilate right.

I’m glad that the TIME do not accept the value you want, if you accept would probably use wrong without realizing, falling into what I always say:

Otherwise I can’t imagine why 39.37 is the same as 39 hours and 50 minutes. .37 is just over 1/3, so it should be just over 20 minutes when we talk about an hour.