MS SQL, how to record negative time

Question

I have a MS SQL database column of type TIME. I need to write a negative value in it. This is not a range between two specific times or dates. Its abstract meaning of time, which is used in calculations. In the entity structure, it is considered as a TimeSpan, which MetaData.tt performs automatically with all defined TIME columns in the database.

For example, I might have a custom calendar with events at 5am and 8pm on Monday, one at 4pm on Tuesday and one at 3am on Sunday after that. I could add a value to these times and get the time either before (in the case of a negative time) or after (in the case of a positive time) the start of the event.

When I try to write a negative value, the database rejects it.

Writing an object to the database is done by directly linking post-attributes in the controller, so if it needs to be converted to ticks, is it reliable to do this in Javascript? How can I use an input text box for it? It seems like I cannot separate the value from the content in the text box. And if I need to turn it into INT, I can no longer use @EditorFor, creating another error where the code becomes less flexible.

It seems to me that I should create new columns to indicate the negativity of these values ​​and use a drop-down list with hidden inputs instead of a text field.

EDIT: Why avoid non-urgent types:

Consider this code:

var typeName = _code.Escape(edmType.MetadataProperties.Where(…).FirstOrDefault()); 

If the EDM property is of type int, the generated code will be of type int. The EDM property comes from the database itself, so if it is not a type that translates directly in time, then there must be a new method (perhaps somewhere in the helper) that translates this in time. This new method should be supported (by other people on the team), which means a weak point, because if someone changes the column name, now the code will not just be generated correctly again.

Errors can also be unavailable through the error log, since most properties usually refer to javascript at some point (which is often also generated and now cannot be for this column, because this is a special case), I am also talking about there are about 20 columns suffering from it, so it has very good potential to quickly turn into a deeply tangled ball of spaghetti.

Answer 1

It seems that you are trying to keep the duration, not the time. TIME used to store a point in time, not an amount of time. I would choose some time division (second, millisecond, etc.) and save it as int (or bigint if necessary). Then in SQL Server you can use DATEADD(SECOND,@storedValue,@dateToChange) to calculate the true time or DateTime.Add.Milliseconds(storedValue) or DateTime.Add.Seconds(storedValue) and so on in C # when trying to calculate the time which you want.

Let me know if I missed something.

Answer 2

In these cases, I think I would save both Begin and End times, and another Computed Column would save the difference (with the INT data type) using DATEDIFF:

 CREATE TABLE dbo.MyTable ( Time1 time Time2 time timedifference AS DATEDIFF(Second, Time1,Time2) ); 

Then you can convert seconds during the day as follows:

 SELECT CONVERT(varchar, DATEADD(ms, timedifference * 1000, 0), 114) 

Here is a working example of what you get:

 SELECT CONVERT(varchar, DATEADD(ms, DATEDIFF(Second, CAST('12:24:18.3500000' as time),CAST('11:24:18.3500000' as time)) * 1000, 0), 114) SELECT CONVERT(varchar, DATEADD(ms, DATEDIFF(Second, CAST('11:24:18.3500000' as time),CAST('12:24:18.3500000' as time)) * 1000, 0), 114) 

Answer 3

The time database type does not support negative values. Valid Range: 00:00:00.0000000 to 23:59:59.9999999

https://msdn.microsoft.com/en-us/library/bb677243.aspx