CODEWITHSUNDEEP
c#sql-serverentity-framework
pradeep Gavhane
pradeep Gavhane

Reputation: 25

How to Save TimeSpan value in database in "d.hh:mm:ss" format?

TimeSpan timeInDays= new TimeSpan(2,8,8,8);

i want to save "timeInDays" in DB using EF. Table column is of type "Time". It gives me run time exception,

"SqlDbType.Time overflow. Value '2.08:08:08' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999."

Please help me to solve this problem.

Upvotes: 1

Views: 9557

Answers (3)

Dai
Dai

Reputation: 155503

As @Mainul says, the time type has a range of 24 hours at the most. To store longer time periods I recommend using bigint (a 64-bit integer) that can be used to represent a timespan value directly. See this QA for more details: What is the correct SQL type to store a .Net Timespan with values > 24:00:00?

If you can't change the table schema then you'll have to truncate data, one approach is to lose less-significant bits by shifting to the right before saving to the DB (and then left-shifting when loading), but this will be messy:

The time type in SQL Server by default is time(7) (7 decimal digits of precision) which uses 5 bytes, see here: https://msdn.microsoft.com/en-us/library/bb677243.aspx - so we need to shift the binary representation of a timespan ("ticks"), which uses 8 bytes, to the right by 3 bytes (to get 5), which is 24 bits. Illustrated below:

TimeSpan tick value (64-bits): 0x01 0x23 0x45 0x67 0x89 0xAB 0xCD 0xEF
SQL Server time field        :                0x01 0x23 0x45 0x67 0x89

As TimeSpan Tick store their most-significant bits on the left, it means the three bytes that we will lose (0xAB 0xCD 0xEF) represent tiny fractions of a second that we can afford to lose that aren't worth worrying about. When we load the data back from the DB and left-shit it back by 3 bytes it will look like this (note the three 0x00 0x00 0x00 bytes that have lost their values):

TimeSpan tick value (64-bits): 0x01 0x23 0x45 0x67 0x89 0x00 0x00 0x00

Saving:

TimeSpan tooBig = new TimeSpoan( 2, 8, 8, 8 );
Int64 tooBigBits = tooBig.Ticks;
Int64 truncated = tooBigBits >> 24;
TimeSpan temp = TimeSpan.FromTicks ( truncated );

yourDbEntity.timeValue = temp;

Loading:

TimeSpan temp = yourDbEntity.timeValue;
Int64 truncated = temp.Ticks;
Int64 adjusted = truncated << 24;
TimeSpan actual = TimeSpan.FromTicks( adjusted );

yourDbEntity.timeValue = actual;

Upvotes: 3

J&#252;rgen Steinblock
J&#252;rgen Steinblock

Reputation: 31743

You could store your time as a DateTime (02.01.0001 08:08:08) in the db and still use the time for your project

public class Task
{
    [NotMapped] 
    public TimeSpan Duration
    {
        get { return Duration2 - DateTime.MinValue; }
        set { Duration2 = DateTime.MinValue + value; }
    }

    public DateTime Duration2 { get; set; }
}

This has the disadvantage that you can't make database queries based on Duration 

var tasks = from t in tasks where t.Duration > TimeSpan.FromHours(50);

but you have to use

var duration = DateTime.MinValue + TimeSpan.FromHours(50);
var tasks = from t in tasks where t.Duration2 > duration;

Or you could store seconds instead of a date value in your database with the same concept.

Upvotes: 1

Mainul
Mainul

Reputation: 889

In T-SQL, Time type column Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock. https://msdn.microsoft.com/en-us/library/bb677243.aspx

Your TimeSpan() method return time including day value for which it is greater than a 24 hour value. What you can do is, save the time value without the day value.

EDIT: If you want to subtract the day value from current TimeSpan.

new TimeSpan(timeInDays.Hours, timeInDays.Minutes, timeInDays.Seconds)

Upvotes: 2

Related Questions