Here is a quick rummage in the innards of Microsoft SQL Server's DateTime type - I have intentionally kept wordiness to a minimum - it's all there in the code.
print 'datetime is represented by eight bytes of data';
print '==============================================';
declare @now as datetime;
set @now = getdate();
declare @now_raw as binary( 8 );
set @now_raw = cast( @now as binary( 8 ) );
select
@now [@now: getdate()]
, @now_raw [@now_raw: cast( @now as binary( 8 ) )];
/*****************************************************************************
*****************************************************************************/
print 'the base datetime is midnight ( as in beginning of ) 1st January 1900';
print '=====================================================================';
declare @base as datetime ;
set @base = cast( 0 as datetime );
select
@base [@base: cast( 0 as datetime )]
, cast( @base as binary( 8 ) ) [cast( @base as binary( 8 ) )];
/*****************************************************************************
*****************************************************************************/
print 'these eight bytes represent two four byte integers';
print '==================================================';
declare @days_raw binary( 4 );
declare @ticks_raw binary( 4 );
/* note: substring( value, start, length ) is offset from 1 */
set @days_raw = substring( @now_raw, 1, 4 );
set @ticks_raw = substring( @now_raw, 5, 4 );
select
@days_raw [@days_raw: substring( @now_raw, 1, 4 )]
, @ticks_raw [@ticks_raw: substring( @now_raw, 5, 4 )];
/*****************************************************************************
*****************************************************************************/
print 'the first represents the number of days since the base date';
print '===========================================================';
declare @days int;
set @days = cast( @days_raw as int );
select
datediff( day, @base, @now ) [datediff( day, @base, @now )]
, @days [@days: cast( @days_raw as int )];
/*****************************************************************************
*****************************************************************************/
print 'if padded correctly it can just be cast to datetime';
print '===================================================';
select
cast( @days_raw + 0x00000000 as datetime ) [cast( @days_raw + 0x00000000 as datetime )];
/*****************************************************************************
*****************************************************************************/
print 'the date value can be separated from the time without doing binary manipulation';
print 'note: 0 is implicitly converted to the base datetime';
print '===============================================================================';
declare @today datetime;
set @today = dateadd( day, datediff( day, 0, @now ), 0 );
select
@today [@today: dateadd( day, datediff( day, 0, @now ), 0 )];
/*****************************************************************************
*****************************************************************************/
print 'the second is the number of ticks since midnight, a tick is 1/300 seconds long';
print '==============================================================================';
declare @ticks int;
set @ticks = cast( @ticks_raw as int );
select
datediff( second, @today, @now ) [datediff( second, @today, @now )]
, @ticks [@ticks: cast( @ticks_raw as int )]
, @ticks / 300 [@ticks / 300]
, @ticks / 300.0 [@ticks / 300.0];
/*****************************************************************************
*****************************************************************************/
print 'a datetime can be cast to a float representing number of days since base';
print '========================================================================';
select
cast( @now as float ) [cast( @now as float )];
/*****************************************************************************
*****************************************************************************/
print 'this can be checked by constructing a float from the days and ticks values';
print '==========================================================================';
/* note: float conversion prevents data loss during integer division */
select
@days + ( @ticks / cast( ( 300 * 60 * 60 * 24 ) as float ) ) [@days + ( @ticks / cast( ( 300 * 60 * 60 * 24 ) as float ) )];
/*****************************************************************************
*****************************************************************************/
print 'which means that [ cast( cast( @date_time_value as float ) as datetime ) == @date_time_value ] should be invariant';
print '==================================================================================================================';
select
@now [@now]
, cast( cast( @now as float ) as datetime ) [cast( cast( @now as float ) as datetime )]
print 'and hence that the mean of a set of datetime values can be calculated by casting to float';
print '=========================================================================================';
print 'create table #temp_date ( value datetime );'
print '';
create table #temp_date ( value datetime );
print 'insert into #temp_date ( value ) values ( getdate() );';
insert into #temp_date ( value ) values ( getdate() );
print 'insert into #temp_date ( value ) values ( ''1980-01-19'' );';
insert into #temp_date ( value ) values ( '1980-01-19' );
print 'insert into #temp_date ( value ) values ( ''2000-01-01'' );';
insert into #temp_date ( value ) values ( '2000-01-01' );
print '#temp_date:';
select t.value from #temp_date t;
select cast( avg( cast( t.value as float ) ) as datetime ) [cast( avg( cast( t.value as float ) ) as datetime )]
from #temp_date t;
drop table #temp_date;
Running this lot gave me the following:
datetime is represented by eight bytes of data
==============================================
@now: getdate() @now_raw: cast( @now as binary( 8 ) )
----------------------- -------------------------------------
2010-11-22 13:51:35.577 0x00009E3600E46761
(1 row(s) affected)
the base datetime is midnight ( as in beginning of ) 1st January 1900
=====================================================================
@base: cast( 0 as datetime ) cast( @base as binary( 8 ) )
---------------------------- ----------------------------
1900-01-01 00:00:00.000 0x0000000000000000
(1 row(s) affected)
these eight bytes represent two four byte integers
==================================================
@days_raw: substring( @now_raw, 1, 4 ) @ticks_raw: substring( @now_raw, 5, 4 )
-------------------------------------- ---------------------------------------
0x00009E36 0x00E46761
(1 row(s) affected)
the first represents the number of days since the base date
===========================================================
datediff( day, @base, @now ) @days: cast( @days_raw as int )
---------------------------- -------------------------------
40502 40502
(1 row(s) affected)
if padded correctly it can just be cast to datetime
===================================================
cast( @days_raw + 0x00000000 as datetime )
------------------------------------------
2010-11-22 00:00:00.000
(1 row(s) affected)
the date value can be separated from the time without doing binary manipulation
note: 0 is implicitly converted to the base datetime
===============================================================================
@today: dateadd( day, datediff( day, 0, @now ), 0 )
---------------------------------------------------
2010-11-22 00:00:00.000
(1 row(s) affected)
the second is the number of ticks since midnight, a tick is 1/300 seconds long
==============================================================================
datediff( second, @today, @now ) @ticks: cast( @ticks_raw as int ) @ticks / 300 @ticks / 300.0
-------------------------------- --------------------------------- ------------ ---------------------------------------
49895 14968673 49895 49895.576666
(1 row(s) affected)
a datetime can be cast to a float representing number of days since base
========================================================================
cast( @now as float )
----------------------
40502.5774951003
(1 row(s) affected)
this can be checked by constructing a float from the days and ticks values
==========================================================================
@days + ( @ticks / cast( ( 300 * 60 * 60 * 24 ) as float ) )
------------------------------------------------------------
40502.5774951003
(1 row(s) affected)
which means that [ cast( cast( @date_time_value as float ) as datetime ) == @date_time_value ] should be invariant
==================================================================================================================
@now cast( cast( @now as float ) as datetime )
----------------------- -----------------------------------------
2010-11-22 13:51:35.577 2010-11-22 13:51:35.577
(1 row(s) affected)
and hence that the mean of a set of datetime values can be calculated by casting to float
=========================================================================================
create table #temp_date ( value datetime );
insert into #temp_date ( value ) values ( getdate() );
(1 row(s) affected)
insert into #temp_date ( value ) values ( '1980-01-19' );
(1 row(s) affected)
insert into #temp_date ( value ) values ( '2000-01-01' );
(1 row(s) affected)
#temp_date:
value
-----------------------
2010-11-22 13:51:35.577
1980-01-19 00:00:00.000
2000-01-01 00:00:00.000
(3 row(s) affected)
cast( avg( cast( t.value as float ) ) as datetime )
---------------------------------------------------
1996-12-24 04:37:11.857
(1 row(s) affected)
Note: rounding errors sometimes creep into the purportedly invariant condition stated above so watch for the stray 5 milliseconds or so!
No comments:
Post a Comment