SQL Server Datetime vs.Datetime2

SQL Serverには、範囲、精度、ストレージサイズ、およびユーザー定義の小数秒の精度を持つ多くの時刻と日付の形式があります。

以下は簡単な概要です:

私たちは、datetimeとdatetime2形式の比較に焦点を当てたいと思います。 私の現在の会社では、datetimeを使用する多くのレガシーテーブルに遭遇します。 Datetime2はSQL Server2008で最初に導入されました。 しかし、一部の開発者は、datetime2の長所と短所について単に知らないと思います。

Datetime

最初にdatetimeについて少し説明しましょう。 上記のように、8バイトのストレージが必要で、1753-01-01から9999-12-31までの範囲があります。 特に、それは後方に短い範囲を持っています。 これは、イギリスが1752年に数日をスキップしてユリウス暦からグレゴリオ暦に移行したためです。 より正確には、1752年9月2日に続いて1752年9月14日に続いた。 1753より前の日付はあいまいであるため、datetime型は1753より前は無効です。 Datetimeデータ型のもう1つの非常に顕著な特性は、実際には1秒の1/300である0.00333秒の精度です。

これは少し奇妙に思えます。 私たちはdatetimeでミリ秒の精度を持っていません。 OK、しかし、なぜ? Datetimeデータ型を詳細に分析しましょう。 Datetimeでは、日付に4バイト、時間に4バイトを使用します。 それはどのように正確に動作しますか? 見てみよう

DECLARE @test DATETIME = '2015-11-29 10:00:00.000';SELECT CAST(@test as varbinary(8))> 0x0000A55F00A4CB80

だから0x0000A55F00A4CB80は十六進です。 私たちは二つの部分に8バイトを分離してみましょう。 最初の日付。 0x0000A55Fは日付を表します。 10進数では42335です。 これは、1900-01-01から経過した日数です。 証明:

SELECT DATEADD(DD,42335,'1900-01-01')> 2015-11-29

今のところ、最後の4バイト0xA4CB80が10進数に変換されているのは10800000です。 それは真夜中から10800000ティックを意味します。 私は精度が秒の1/300であると言った覚えていますか? これは、datetimeが時刻をティックで格納するためです。 したがって、真夜中からの10800000ティックは、10800000倍の1/300秒を意味します。 少し計算してみましょう。

だから私たちは真夜中から正確に10時間を持っており、それは10:00:00に完全に変換されます。 私たちは2015-11-29 10:00:00を持っている日付と組み合わせます。

datetimeは常に8バイトのストレージを使用し、日付を表す最初の4バイトは1900より前である可能性があるため、負(2complement)になる可能性があることに注意してく たとえば、1890-11-29では、最初の4バイトは0xfffff308として取得され、32ビットの2補数として-3320に変換されます。 そして、3320は1900-01-01から1890-11-29です。

datetime2

SQL Server2008で導入されたすべての日付と時刻のデータ型には、ここで検討する完全に新しいストレージ型があります。 Datetime2データ型は、ミリ秒の精度に応じて6~8バイトを使用します。

DECLARE @test DATETIME2(3) = '2015-11-29 10:00:00.000';SELECT CAST(@test as varbinary(8))> 0x0300512502BA3A0B

今回はもう少し複雑になります。 すべての新しいdatetimeデータ型では、最後の3バイトが日付を表します。 これはバイト順の変更によるものです。 つまり、最上位バイトは左端にあり、ビッグエンディアンでは最上位バイトは右端の位置に格納されます。

つまり、0x0300512502ba3a0bを取るとき、日付は0xba3a0bではなく0x0b3abaであることを意味します。

再び数学で:0x0b3abaは10進数735930を表します。 これはまさに私たちが望んでいた日付です:

SELECT DATEADD(DD,735930,CAST('0001-01-01' as date))> 2015-11-29

バイトが変換されたので、0x0225510003であるリトルエンディアン表現の最後のバイトを取ることができます。 リトルエンディアンの最後のバイト(つまり、元のビッグエンディアンの最初のバイト)が記載されている精度であることに注意してください。 ご覧のとおり、最後のバイトが0x03であることを意味するdatetime2(3)を定義しました。

数学を行う:0x02255100は10進数で36000000です。 私たちは3桁の精度を意味する精度3を使用していたので、私たちの場合は103である精度のべき乗に10で割ることによって最初に秒を計算します。

これはまた、10時間0分0秒に完全に変換されます。最後に、これら2つのデータ型間の単純で単純な比較。

日時 日時2
1/300の最大精度奇数精度 100ナノ秒精度
ユーザー定義の精度 いいえ はい0から6441までの範囲7
ストレージスペース 常に8バイト 精度に応じて6-8バイト
+または-演算子で使用可能 はい いいえ、datediff、dateaddなどを使用します。
SQL標準互換 いいえ はい

したがって、全体的に見ると、datetimeは潜在的に多くのストレージを使用し、精度が低く、精度が低く、範囲が低く、SQL標準と互換性がないため、コードはDBMSごとに したがって、アプリケーションがdate、datetime2、datetimeoffsetをサポートしている場合は、新しいdatetimeデータ型を使用することを強くお勧めします。

お時間をありがとう。 より興味深い記事はhttp://www.dirtyread.de/

をご覧ください

コメントを残す

メールアドレスが公開されることはありません。