简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
Heim > Datenbank > MySQL-Tutorial > Hauptteil

解剖SQLSERVER 第四篇 OrcaMDF里对dates类型数据的解析（译）

WBOY
Freigeben： 2016-06-07 15:19:42
Original
1125 Leute haben es durchsucht

解剖SQLSERVER 第四篇 OrcaMDF里对dates类型数据的解析（译） http://improve.dk/parsing-dates-in-orcamdf/ 在SQLSERVER里面有几种不同的date相关类型，当前OrcaMDF 支持三种最常用的date类型：date，datetime，smalldatetime SqlDate实现 date 类型在三种

解剖SQLSERVER 第四篇  OrcaMDF里对dates类型数据的解析（译）

http://improve.dk/parsing-dates-in-orcamdf/

在SQLSERVER里面有几种不同的date相关类型，当前OrcaMDF 支持三种最常用的date类型：date，datetime，smalldatetime

 

SqlDate实现

date 类型在三种类型之中是最简单的，他是一个3个字节的定长类型，存储了日期值它支持的日期范围从0001-01-01到9999-12-31

默认值是1900-01-01

比较坑爹的是.NET里面还没有任何标准实现能够支持3个字节的整数类型，只有short类型和int类型，但是，他们要不太大要不太小

另外，要正确读取日期值，对于.NET的4字节整型我们必须执行一些转变去获取正确的数字

一旦我们获取到date的值，我们可以创建一个默认的datetime类型并且添加天数进去

<span>public</span> <span>class</span><span> SqlDate : ISqlType
{
    </span><span>public</span> <span>bool</span><span> IsVariableLength
    {
        </span><span>get</span> { <span>return</span> <span>false</span><span>; }
    }

    </span><span>public</span> <span>short</span>?<span> FixedLength
    {
        </span><span>get</span> { <span>return</span> <span>3</span><span>; }
    }

    </span><span>public</span> <span>object</span> GetValue(<span>byte</span><span>[] value)
    {
        </span><span>if</span> (value.Length != <span>3</span><span>)
            </span><span>throw</span> <span>new</span> ArgumentException(<span>"</span><span>Invalid value length: </span><span>"</span> +<span> value.Length);

        </span><span>//</span><span> Magic needed to read a 3 byte integer into .NET's 4 byte representation.
        </span><span>//</span><span> Reading backwards due to assumed little endianness.</span>
        <span>int</span> date = (value[<span>2</span>] 16) + (value[<span>1</span>] 8) + value[<span>0</span><span>];

        </span><span>return</span> <span>new</span> DateTime(<span>1</span>, <span>1</span>, <span>1</span><span>).AddDays(date);
    }
}</span>
Nach dem Login kopieren

相关测试

<span>using</span><span> System;
</span><span>using</span><span> NUnit.Framework;
</span><span>using</span><span> OrcaMDF.Core.Engine.SqlTypes;
</span><span>namespace</span><span> OrcaMDF.Core.Tests.Engine.SqlTypes
{
    [TestFixture]
</span><span>public</span> <span>class</span><span> SqlDateTests
{
        [Test]
</span><span>public</span> <span>void</span><span> GetValue()
{
</span><span>var</span> type = <span>new</span><span> SqlDate();
</span><span>var</span> input = <span>new</span> <span>byte</span>[] { <span>0xf6</span>, <span>0x4c</span>, <span>0x0b</span><span> };
Assert.AreEqual(</span><span>new</span> DateTime(<span>2028</span>, <span>09</span>, <span>09</span><span>), Convert.ToDateTime(type.GetValue(input)));
input </span>= <span>new</span> <span>byte</span>[] { <span>0x71</span>, <span>0x5c</span>, <span>0x0b</span><span> };
Assert.AreEqual(</span><span>new</span> DateTime(<span>2039</span>, <span>07</span>, <span>17</span><span>), Convert.ToDateTime(type.GetValue(input)));
}
        [Test]
</span><span>public</span> <span>void</span><span> Length()
{
</span><span>var</span> type = <span>new</span><span> SqlDate();
Assert.Throws</span><argumentexception>(() => type.GetValue(<span>new</span> <span>byte</span>[<span>2</span><span>]));
Assert.Throws</span><argumentexception>(() => type.GetValue(<span>new</span> <span>byte</span>[<span>4</span><span>]));
}
}
}</span></argumentexception></argumentexception>
Nach dem Login kopieren

 

SqlDateTime实现

date类型只能存储日期，而datetime类型不但能存储date也能存储time

datetime存储8字节定长数据值，第一部分是time（4字节），而第二部分是date（4字节）

计算date部分跟上面介绍date类型基本上一样，不过这一次date部分是一个四字节整数，比上面的例子容易处理多了，上面的date类型是3个字节

time部分存储为自午夜时的ticks数，一个tick就是1/300th 秒，为了显示tick值，我们首先定义一个常量，常量值是10d/3d

time的各个部分实际同样存储在同一个整型值里面（比如时间，分钟，秒，毫秒），所以我们要独立访问这些单独的部分，我们必须

要执行一些转换 （包括取模和相除）

<span>部分     计算
小时   X </span>/ <span>300</span> / <span>60</span> / <span>60</span><span>
分钟   X </span>/ <span>300</span> / <span>60</span> % <span>60</span><span>
秒     X </span>/ <span>300</span> % <span>60</span><span>
毫秒   X </span>% <span>300</span> * 10d / 3d
Nach dem Login kopieren

<span>public</span> <span>class</span><span> SqlDateTime : ISqlType
{
    </span><span>private</span> <span>const</span> <span>double</span> CLOCK_TICK_MS = 10d/<span>3d;

    </span><span>public</span> <span>bool</span><span> IsVariableLength
    {
        </span><span>get</span> { <span>return</span> <span>false</span><span>; }
    }

    </span><span>public</span> <span>short</span>?<span> FixedLength
    {
        </span><span>get</span> { <span>return</span> <span>8</span><span>; }
    }

    </span><span>public</span> <span>object</span> GetValue(<span>byte</span><span>[] value)
    {
        </span><span>if</span> (value.Length != <span>8</span><span>)
            </span><span>throw</span> <span>new</span> ArgumentException(<span>"</span><span>Invalid value length: </span><span>"</span> +<span> value.Length);

        </span><span>int</span> time = BitConverter.ToInt32(value, <span>0</span><span>);
        </span><span>int</span> date = BitConverter.ToInt32(value, <span>4</span><span>);

        </span><span>return</span> <span>new</span> DateTime(<span>1900</span>, <span>1</span>, <span>1</span>, time/<span>300</span>/<span>60</span>/<span>60</span>, time/<span>300</span>/<span>60</span>%<span>60</span>, time/<span>300</span>%<span>60</span>, (<span>int</span>)Math.Round(time%<span>300</span>*<span>CLOCK_TICK_MS)).AddDays(date);
    }
}</span>
Nach dem Login kopieren

相关测试

<span>using</span><span> System;
</span><span>using</span><span> NUnit.Framework;
</span><span>using</span><span> OrcaMDF.Core.Engine.SqlTypes;
</span><span>namespace</span><span> OrcaMDF.Core.Tests.Engine.SqlTypes
{
    [TestFixture]
</span><span>public</span> <span>class</span><span> SqlDateTimeTests
{
        [Test]
</span><span>public</span> <span>void</span><span> GetValue()
{
</span><span>var</span> type = <span>new</span><span> SqlDateTime();
</span><span>byte</span><span>[] input;
input </span>= <span>new</span> <span>byte</span>[] { <span>0x5e</span>, <span>0x3b</span>, <span>0x5d</span>, <span>0x00</span>, <span>0x25</span>, <span>0x91</span>, <span>0x00</span>, <span>0x00</span><span> };
Assert.AreEqual(</span><span>new</span> DateTime(<span>2001</span>, <span>09</span>, <span>25</span>, <span>05</span>, <span>39</span>, <span>26</span>, <span>820</span><span>), (DateTime)type.GetValue(input));
input </span>= <span>new</span> <span>byte</span>[] { <span>0xb6</span>, <span>0x87</span>, <span>0xf0</span>, <span>0x00</span>, <span>0xd1</span>, <span>0x8b</span>, <span>0x00</span>, <span>0x00</span><span> };
Assert.AreEqual(</span><span>new</span> DateTime(<span>1997</span>, <span>12</span>, <span>31</span>, <span>14</span>, <span>35</span>, <span>44</span>, <span>607</span><span>), (DateTime)type.GetValue(input));
input </span>= <span>new</span> <span>byte</span>[] { <span>0x2d</span>, <span>0xfd</span>, <span>0x1c</span>, <span>0x01</span>, <span>0x4a</span>, <span>0x75</span>, <span>0x00</span>, <span>0x00</span><span> };
Assert.AreEqual(</span><span>new</span> DateTime(<span>1982</span>, <span>03</span>, <span>18</span>, <span>17</span>, <span>17</span>, <span>36</span>, <span>790</span><span>), (DateTime)type.GetValue(input));
input </span>= <span>new</span> <span>byte</span>[] { <span>0xff</span>, <span>0x81</span>, <span>0x8b</span>, <span>0x01</span>, <span>0x7f</span>, <span>0x24</span>, <span>0x2d</span>, <span>0x00</span><span> };
Assert.AreEqual(</span><span>new</span> DateTime(<span>9999</span>, <span>12</span>, <span>31</span>, <span>23</span>, <span>59</span>, <span>59</span>, <span>997</span><span>), (DateTime)type.GetValue(input));
}
        [Test]
</span><span>public</span> <span>void</span><span> Length()
{
</span><span>var</span> type = <span>new</span><span> SqlDateTime();
Assert.Throws</span><argumentexception>(() => type.GetValue(<span>new</span> <span>byte</span>[<span>9</span><span>]));
Assert.Throws</span><argumentexception>(() => type.GetValue(<span>new</span> <span>byte</span>[<span>7</span><span>]));
}
}
}</span></argumentexception></argumentexception>
Nach dem Login kopieren

 

 

SqlSmallDateTime实现

Smalldatetime 是一个不错的数据类型当你需要存储范围值内的日期值(1900~2079)并且他能精确到秒

大多数场景下，精确到秒已经足够了，在一个范围的时间间隔内和精确值不需要太精确的情况下会节省很多空间

smalldatetime 数据类型会只占用4个字节，前2个字节存储自午夜的分钟数，后2个字节存储日期，默认值是1900-1-1

处理的方法跟datetime差不多，只不过使用更小的范围

<span>部分     计算
小时    X </span>/ <span>60</span><span>
分钟    X </span>% <span>60</span>
Nach dem Login kopieren

<span>public</span> <span>class</span><span> SqlSmallDateTime : ISqlType
{
    </span><span>public</span> <span>bool</span><span> IsVariableLength
    {
        </span><span>get</span> { <span>return</span> <span>false</span><span>; }
    }

    </span><span>public</span> <span>short</span>?<span> FixedLength
    {
        </span><span>get</span> { <span>return</span> <span>4</span><span>; }
    }

    </span><span>public</span> <span>object</span> GetValue(<span>byte</span><span>[] value)
    {
        </span><span>if</span> (value.Length != <span>4</span><span>)
            </span><span>throw</span> <span>new</span> ArgumentException(<span>"</span><span>Invalid value length: </span><span>"</span> +<span> value.Length);

        </span><span>ushort</span> time = BitConverter.ToUInt16(value, <span>0</span><span>);
        </span><span>ushort</span> date = BitConverter.ToUInt16(value, <span>2</span><span>);

        </span><span>return</span> <span>new</span> DateTime(<span>1900</span>, <span>1</span>, <span>1</span>, time / <span>60</span>, time % <span>60</span>, <span>0</span><span>).AddDays(date);
    }
}</span>
Nach dem Login kopieren

相关测试

<span>using</span><span> System;
</span><span>using</span><span> NUnit.Framework;
</span><span>using</span><span> OrcaMDF.Core.Engine.SqlTypes;
</span><span>namespace</span><span> OrcaMDF.Core.Tests.Engine.SqlTypes
{
    [TestFixture]
</span><span>public</span> <span>class</span><span> SqlSmallDateTimeTests
{
        [Test]
</span><span>public</span> <span>void</span><span> GetValue()
{
</span><span>var</span> type = <span>new</span><span> SqlSmallDateTime();
</span><span>var</span> input = <span>new</span> <span>byte</span>[] { <span>0xab</span>, <span>0x02</span>, <span>0x5d</span>, <span>0x26</span><span> };
Assert.AreEqual(</span><span>new</span> DateTime(<span>1926</span>, <span>11</span>, <span>22</span>, <span>11</span>, <span>23</span>, <span>0</span><span>), Convert.ToDateTime(type.GetValue(input)));
input </span>= <span>new</span> <span>byte</span>[] { <span>0x49</span>, <span>0x03</span>, <span>0x99</span>, <span>0x09</span><span> };
Assert.AreEqual(</span><span>new</span> DateTime(<span>1906</span>, <span>9</span>, <span>24</span>, <span>14</span>, <span>1</span>, <span>0</span><span>), Convert.ToDateTime(type.GetValue(input)));
}
        [Test]
</span><span>public</span> <span>void</span><span> Length()
{
</span><span>var</span> type = <span>new</span><span> SqlSmallDateTime();
Assert.Throws</span><argumentexception>(() => type.GetValue(<span>new</span> <span>byte</span>[<span>3</span><span>]));
Assert.Throws</span><argumentexception>(() => type.GetValue(<span>new</span> <span>byte</span>[<span>5</span><span>]));
}
}
}</span></argumentexception></argumentexception>
Nach dem Login kopieren

 

第四篇完

Verwandte Etiketten：
dat sqlserver
Quelle：php.cn
Vorheriger Artikel：SqlServer特殊字符转换&amp;amp;查询 Nächster Artikel：解剖SQLSERVER 第十二篇 OrcaMDF 行压缩支持（译）
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Neueste Artikel des Autors
Aktuelle Ausgaben
Verschieben Sie den Inhalt nach links, indem Sie die Eigenschaft „Width' hinzufügen Ich habe dem Körper Ränder gegeben. main {linker Rand: 200px; rechter Rand: 200px; Textaus...
Aus 2024-04-06 22:01:35
0
3
816
So lösen Sie das mysteriöse Leerzeichen am unteren Rand der Seite in CSS Ich versuche, mit Bootstrap und D3 eine einfache Webseite zu erstellen, weiß aber nicht, w...
Aus 2024-04-06 20:22:15
0
1
454
Berechnen Sie die Summe der Felder in einer anderen Tabelle mithilfe einer MySQL-SQL-Abfrage Ich habe ein Schema wie dieses: Benutzertabelle mit den Attributen „user_id“ und „username...
Aus 2024-04-06 19:39:29
0
1
441
So erhalten Sie den Vornamen aus der Spalte „customer_name'. selectsubstring(custommer_name,1,instr(custmer_name,''))asfirst_namefromsales.customers; d...
Aus 2024-04-06 17:43:30
0
2
433
Generieren Sie Standardwerte und CSS-Variablen mit SCSS Ich setze Website-Styling um. Aus Gründen der Legacy-Unterstützung muss ich IE11 zumindest...
Aus 2024-04-06 17:46:54
0
1
355
verwandte Themen
Mehr>
Beliebte Empfehlungen
Beliebte Tutorials
Mehr>
Verwandte Tutorials
Beliebte Empfehlungen
Aktuelle Kurse
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage
Über uns Haftungsausschluss Sitemap
Chinesische PHP-Website：Online-PHP-Schulung für das Gemeinwohl，Helfen Sie PHP-Lernenden, sich schnell weiterzuentwickeln！