在SQL Server CE中表示时间跨度的最佳方法是什么？

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?

具体来说,我只需要几小时:分钟但是说我有一个.NET TimeSpan对象,我应该如何将它存储在SQL(CE)数据库中?

4 个解决方案

#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

我建议使用long来表示刻度数。这就是TimeSpan使用的内部表示。这使您可以使用Timespan.FromTicks()方法轻松地重构对象,并使用Timespan.Ticks属性输出到数据库。 .NET中最小的时间单位是tick,等于100纳秒

#2

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.

SQL CE没有时间类型或用户定义的类型,因此您的选择是datetime或表示分钟的int。如果您需要存储的最长时间是23:59 = 23 * 60 + 59 = 1439 =从第0分钟开始的一天中的分钟数,则smallint是适应该范围的最小整数类型。

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.

抵制将小时和分钟存储在单独的列中作为tinyints的诱惑。这将使用相同的空间作为单个smallint,但是每次计算时间都需要将小时乘以60并添加分钟,并且每个订单将需要两列而不是一列。

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.

如果要将一天中的1439分钟与一小时内的0-59分钟区分开来,我会将列命名为分钟或绝对分钟(绝对分钟)。

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 ).

要从数据库值转换为Timespan对象,请使用ctor Timespan(int,int,int),如新的TimeSpan(floor(absminutes / 60),absminutes%60,0)或(更好)ctor Timespan(long)使用新的Timespan(absminutes * TimeSpan.TicksPerMinute)。

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

要从Timespan对象插入或更新数据库,请将absminutes设置为someTimespan.TotalMinutes%1440。

#3

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

存储为varchar。使用TimeSpan.ToString()保存到sql。从sql读取:

TimeSpanObj = TimeSpan.Parse(fieldValue)

#4

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

我目前正在考虑使用SQL Time来处理这个问题,但它不会在> = 24小时内工作。

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.

好处包括易于阅读,在SQL和代码中都可以轻松使用,但缺点是您只需要很小的时间跨度。

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"];

#1

#2