How to convert 39 hours to type 'TIME'

Question

How to convert 39 hours to type 'TIME'

Navigation

#1 by (4 votes)

1

To accomplish a certain task, I count the amount of time I spend on the task in my system.

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

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

However, according to documentation Time (Transact-SQL)

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

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

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

sql sql-server decimal time

asked by anonymous 19.12.2018 / 11:43

1 answer

Sort by the largest ID and group the similar ones How to increase the distance to display icon that expands menu

score 4 · Accepted Answer

No, type TIME was created to indicate a specific point in the timeline, not to indicate intervals of time that is what you want. This requirement "needs to convert this value (39.3716) to TIME format" part of a wrong premise, it's like wanting to measure how much milk you have in meters.

The correct way to store a time interval is to place the unit amount of time. Probably the most appropriate is the number of seconds, but can be more or less accurate, as long as you know what it is always, it can be only minutes, for example.

.NET has a more specialized type that best handles this for you Timespan . Unfortunately the databases do not usually have a type like this, so you need to control that at hand.

Storing is different from submitting. Many people do not understand this. You can present as you want, you can even take that unit of time contained in the range and present as if it were a time. This is 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 schedule and not elapsed time, but if you want you can do it, of course. You have a question that talks about this .

Either way, how long the calculations take is the correct time is to store it as a number and not as a text. I would go from BIGINT or equivalent in your SGDB to save seconds elapsed in this range. For the presentation I would create functions that do the conversion, something similar can be seen in link above, would just adapt.

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

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

Other than this I can not imagine why 39.37 is the same as 39 hours and 50 minutes. .37 is slightly above 1/3, so it should be little more than 20 minutes when we talk about an hour.