SQL Server Time data type and negative TimeSpan values?

kbalias

Hi I am busy working on a windows application to log working times of staff. The application is being developed using C# with SQL Server 2008 as the backend. I have a [NetTime] column (Time data type) in my table in the database. I get the TimeSpan value by subtracting the official working hours and the actual working hours. This TimeSpan value can be positive or negative. I would like to store this value in the [NetTime] column, but the Time data type does not accept negative values. What is the usual way of dealing with this problem? I will need to do calculations with the values in this column, for example to calculate the net or total time a staff member worked overtime or not. Thank you. Kobus

Wayne Gaylard

My advice would be to use a Long data type to store the Timespan. You can easily store this in the db using TimeSpan.Ticks() and convert back to a TimeSpan in your app using TimeSpan.FromTicks(). That way there is no problem storing positive or negative values. Hope this helps

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

Mycroft Holmes

Most timekeeping systems have a minimum timespan for calculations eg a minute, timespan.tick has no place in a timekeeping system. Therefore I would store the values as integers. Sometimes you need to look at the real world, not the theoretical or technical capabilities of the system.

Never underestimate the power of human stupidity RAH

David Skelly

A Time represents a point in time. A TimeSpan is a duration of time. They are fundamentally different things. 11 o'clock is a point in time, and so is 12 o'clock. The time span between them is 1 hour. An analogy: the location of Paris is at 48°48'N 2°20'E. London is at 51°32'N 0°5'W. The distance between Paris and London is 211 miles. Location and distance are fundamentally different things and are not interchangeable. Similarly, Time and TimeSpan are different things and are not interchangeable.

Jorgen Andersson

A (pretty bad) solution is to store the Abs(Timespan) value in one column and Sign(Timespan) in another. The others have given you better answers .

List of common misconceptions

Corporal Agarn

I almost posted a "bad" solution with a disclaimer like yours. (store it as a character).

jschell

kbalias wrote:

I get the TimeSpan value by subtracting the official working hours and the actual working hours.

Which would seem to me that you would need to store the "official working hours" as well. Given that then there is no point in storing the difference for the second value. Instead you just store the "actual working hours". If you need the difference then the caller subtracts the two. If you want the difference anyways then store it as seconds. That insures enough precision and is probably a more business friendly value than Ticks.

Chris Meech

You could even use the '+' character for positive values and the '-' character for negative ones. I'm not sure what a NULL value would mean though. Might have to make it a non-nullable column. :doh:

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Jorgen Andersson

<Getting my brain back into gear> The standard way is to store the login and logout times in a table. The nettime is easily enough calculated from those two values. It's a part of normalization to not store values you can calculate from other columns in the database.

List of common misconceptions

Jorgen Andersson

The problem for me is that I'm often enough set on the wrong track by how the question is formulated.

List of common misconceptions

Wendelius

I agree with Jörgen. It isn't advisable to store calculated values in the database. If any of the values used in a calculation changes you have to do a recalculation to keep the data up-to-date. Consider the following:

create table timetest (
starttime datetime,
endtime datetime
);

insert into timetest values ( getdate(), getdate() - 1);
insert into timetest values ( getdate(), getdate() + 1);

select starttime, endtime, DATEDIFF(n, starttime, endtime) from timetest;

if either one of the datetime columns change, the result is still always correct. However, what you can do is that you can specify a computed column if you like. To extend the previous example:

alter table timetest add timediff as DATEDIFF(n, starttime, endtime)

Now if you run a select * query to the table you will see the same results as in the previous example because the calculation is now part of the table structure (and always up-to-date).

The need to optimize rises from a bad design.My articles[^]

Wendelius

Agree with this. Answer upvoted.

The need to optimize rises from a bad design.My articles[^]