What is the best way to represent a timespan in SQL Server CE?

Question

Specifically speaking I only need hours:minutes but say I have a .NET TimeSpan object, how should I store that in a SQL(CE) database?

Gatehouse

Answer 1

I'd recommend using a long to represent the number of ticks. That's what TimeSpan uses as it's internal representation. This lets you easily reconstitute your object with the Timespan.FromTicks() method, and output to the database using the Timespan.Ticks property. The smallest unit of time in .NET is the tick, which is equal to 100 nanoseconds

Archibald

That's exactly what I'm doing on the project I'm working on. The ORM layer then does the conversion like you've said, between TimeSpan and ticks, back and forth, with no room for error. Also, a side bonus is that you've got all the precision you'll probably ever need. If you stick with minutes, you've got a real problem if you the marketing department says "How hard would it be to get the precision down to seconds?" – Stubstad

Nice, though is one tick guarantied to take exactly 100 nanoseconds? – Mide

The framework documentation doesn't mention any wiggle room: msdn.microsoft.com/en-us/library/… – Archibald

Answer 2

Store as a varchar. Save to sql using TimeSpan.ToString(). Read from sql as:

TimeSpanObj = TimeSpan.Parse(fieldValue)

Mathi

Why was this downvoted? It's a legitimate suggestion, and if you have to edit the SQL data directly, it's a lot easier to write "2 minutes" as '00:02' instead of 1200000000 – Gumshoe

Answer 3

SQL CE doesn't have a time type, or user defined types, so your choices are datetime or an int representing minutes. If the largest time you need to store is 23:59 = 23 * 60 + 59 = 1439 = the number of minutes in a day starting from minute 0, a smallint is the smallest integral type that will accommodate that range.

Resist the temptation to store hours and minutes in separate columns as tinyints. That would use the same space as a single smallint, but then every calculation of times will require multiplying hours by 60 and adding minutes, and every order by will require two columns instead of one.

Store as minutes; on display, you can separate the minutes into hours and minutes with

select floor( absminutes / 60 ) as hours, absminutes % 60 as minutes,
from some table
order by absminutes; 

I'd name the column(s) minutes, or absminutes (for absolute minutes) if you want to distinguish the 1439 minutes in a day from the 0-59 minutes in a hour.

To convert from the database value to a Timespan object, use the ctor Timespan(int, int, int) like this new TimeSpan( floor(absminutes / 60 ), absminutes % 60, 0) or (better) the ctor Timespan(long) with new Timespan( absminutes * TimeSpan.TicksPerMinute ).

To insert or update the database from a Timespan object, set absminutes to someTimespan.TotalMinutes % 1440.

Ewing

Answer 4

I'm currently considering using SQL Time to handle this, but it won't work for >= 24 hours.

The benefits include easily readable, easily usable in both SQL and in code, but the downfall is that you only have a small timespan to play with.

declare @delay table (DelayTime Time(3))
insert into @delay values ('00:10:00.000')
select getdate() as nowtime, getdate()+DelayTime as nowPlusTen from @delay

and

SqlDataReader dr = cmd.ExecuteReader();
DelayTime = (TimeSpan) dr["DelayTime"];

Patronize