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?

Answer 1

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.

Answer 2

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.

Answer 3

Store as a varchar. Save to sql using TimeSpanObj.ToString(). Read from sql as TimeSpanObj = TimeSpan.Parse(fieldValue).