1、SQL Server日期数据库内部表示及查询输出:
SQL Server内部对DateTime类型的表示是用8个字节来表示,其中前4个字节表示的数值为自1900年1月1日零时以来的天数,后四个字节是天数之外的有多少个1/300秒,这也说明SQL Server内部表示时间的精度为1/300秒,约3.3毫秒。
常规的SQL查询,正常日期输出格式为:yyyy-MM-dd hh:mm:ss.SSS,这也是中文操作系统的日期格式,可以通过修改操作系统日期表达格式来影响SQL Server缺省的日期输出格式。但是,有些时候缺省的日期输出格式并不是我们需要的,特别是基于时间差的应用需求。
2、应用需求:
基于一系列时间点的数据记录分析、统计某种结果。比如GPS定位设备按一定时间间隔回传位置数据并记录到数据库中,数据包括经纬度、时间、速度等,现在需要基于这些回传数据统计是否在某地有停留,停留时间、开始时间、结束时间。停留标志是速度为0。显示,这需要根据数据记录之间的差值做统计计算。
3、适合需求的输出及转换、处理:
如果按照缺省的SQL Server日期输出格式,输出内容可映射为编程语言相关的数据类型,如Java可以是String、Date,转为为Date有一定统计数据量的话,这个转换本身就是低效和耗时的,实际过程可能是先从SQL Server内部表示转换为yyyy-MM-dd hh:mm:ss.SSS字符串,再从这个字符串转换为Date;作为String使用也需要转换为Date之后才可以做时间差运算,同样低效。
SQL Server内部数据表示格式其实已经适合表达时间差了,是不是能直接利用。SQL Server有cast函数,可以将日期输出为二进制数据。如:
select longi, lati, speed, cast(gpsDate as binary(8)) as gpsDate from loc_history
后续处理代码片段为:
float lon = rs.getFloat("longi");
float lat = rs.getFloat("lati");
byte time[] = rs.getBytes("gpsDate");
获得经纬度、时间二进制表示
int days = ((time[0] & 0x00ff) << 24) + ((time[1] & 0x00ff) << 16)
+ ((time[2] & 0x00ff) << 8) + (time[3] & 0x00ff);
int seconds = ((time[4] & 0x00ff) << 24) + ((time[5] & 0x00ff) << 16)
+ ((time[6] & 0x00ff) << 8) + (time[7] & 0x00ff);
seconds /= 300;
将时间转换为天和秒,至此,可以很方便的进行时间差运算了。
如果需要将天、秒时间转换为yyyy-MM-dd hh:mm:ss.SSS格式,基于1900-01-01,使用Calendar和SimpleDateFormat即可完成。
这种使用方式是一定程度上直接使用SQL Server的内部表示格式,去掉不必要的格式转换,从而加快运算速度,缺点是这种处理方式是数据库相关的,换为其他数据库则算法失效。