MS SQL how to record negative time

Question

I have a column in the MS SQL database with type “TIME”. I need to record a negative value in it. It isn't a range between two certain times or dates. It’s an abstract value of time that is used in calculations. In the entity framework it is treated as a TimeSpan, which MetaData.tt does automatically with all TIME defined columns in the database.

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

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

The recording of entity to database goes by direct bind of post attributes in the controller, so if it needs to be translated into ticks, is it reliable to do so in Javascript? How would I go about the input textbox for it? It looks like I cannot separate the value from the content in a text box. And if I have to turn it into an INT, I cannot use @EditorFor on it anymore, creating another point of fault where code becomes less flexible.

It almost feels like I should create new columns to denote the negativity of these values and use a dropdown list with hidden inputs instead of a textbox.

EDIT: Why avoid non-time types:

Consider this code:

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

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

Errors may also not be available through the error log, since most properties also tend to be referenced in javascript at some point (which is often also generated, and now can't be for this column because it is a special case). I'm also talking about some 20 columns suffering from this, so this has a very good potential to quickly turn into a deeply tangled ball of spaghetti.

Answer 1

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

Let me know if I'm missing something.

Answer 2

In these cases I think I would store both Begin and End times and have another Computed Column to store the difference (with INT datatype) using DATEDIFF:

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

Then you can convert the Seconds into a time of day like this:

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

Here is a working sample of what you will 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)