SQL Server 2008の新しい日付データ型
時間の部分がなく、データベースに日付を格納したいか、時間値を格納したいか、より高い精度を望んでいますか?SQL Server 2008の紹介では、マイクロソフトがいくつかの新しい日付データクラスを紹介し、1つの日付、より精度の高い時間値などしか保存できません.これらの新しいデータ型は、日付と時間に関する情報を格納するための追加オプションを提供します.この記事では、これらの新しい日付データ型と、旧バージョンのSQL Serverの既存のデータ型との比較について説明します.
古い日付データ型のレビュー
SQL Server 2008の新しい日付データ型について説明する前に、SQL Server 2005およびそれより古いバージョンで提供されている2つの日付データ型をレビューします.これらの古いデータ型はDATETIMEとSMALLDATETIMEです.この2つのデータ型はSQL Server 2008で使用できます.
DATETIMEデータ型ストレージは、1753年1月1日から9999年12月31日までの日付と時間部分を1つの単列値として格納します.時間部分の精度は3.33ミリ秒で、8バイトのストレージスペースが必要です.SMALLDATETIMEデータ型は、DATETIMEタイプよりもストレージ容量が少なく、4バイトしか必要ありませんが、日付と時間の範囲が小さくサポートされています.SMALLDATETIMEは、1900年1月1日から2079年6月6日までの日付のみをサポートし、時間部分は分までしか正確にできません.
この2つの限られた日付フォーマットは、より早い日付とより高い精度の時間を格納する際の選択を制限しています.これらの不足点は、SQL Server 2008の新しい日付データ型よりも、日付と時間を格納する際に多くの選択肢があるため、異なる日付と時間範囲をサポートする他のデータ構造からのデータ移行が困難です.
DATITIME2
DATETIME 2データ型はDATETIMEデータ型に対する拡張であり、この新しいデータ型は日付を許容できる範囲を拡張し、日付/時間値の時間部分に追加精度を追加する.DATETIME 2列のサポート日は0001-01-01から9999-01-01までです.拡張された日付範囲では、創造的な符号化アルゴリズムを使用することなく、より早い日付を格納できます.
DATETIME 2の時間部分の精度は、DATETIME 2列をどのように定義するかに依存します.時間部分は、時間、分、秒のみの時間値を格納することができます.または、異なる精密な決定でマイクロ秒を格納することができます.最大7桁の小数点以下で、微妙に100ナノ秒まで下に正確にすることができます.
DATETIME 2データ型を使用すると、19(YYY-MM-DD hh:mm:ss)から27(YYYY-MM-DD hh:mm:ss)まで、異なる長さの文字を使用して日付を格納および表示できます.これは、異なる数の精度ストレージに依存して、一定のディスクオーバーヘッドが必要です.DATETIME 2列に異なる精度を格納する場合は、列を定義するときに長さを指定する必要があります.DATETIME 2(X)のように精度を指定できます.ここで、Xは精度の長さを表し、0から7を表すことができます.表示長さと記憶領域の関係を次の表に示します.
上記の表に示すように、DATETIME 2列に日付/時刻値を格納するディスク領域のオーバーヘッドは、時間精度をどのくらい維持するかによって6～8バイトです.
DATETIME 2(3)フォーマットは、旧バージョンのSQL Serverで使用したDATETIMEフォーマットと同じですが、DATETIME 2(3)を使用すると精度が1ミリ秒までサポートされ、旧バージョンのDATETIMEフォーマットは3.33ミリ秒までしかサポートされません.DATETIME 2(3)は7バイトのディスクオーバーヘッドのみで、DATETIMEフォーマットは8バイト必要です.これにより、以前にリリースされたSQL Serverと同じ日付と時間値を格納でき、ディスク領域を節約できます.
DATETIME(7)フォーマットを使用すると、精度が向上したにもかかわらず、1つのテーブルの時間値が一意であることは保証されません.DATETIME(7)フォーマットを使用すると、同じ時間の異なる2つのレコードが同じテーブルに格納される可能性があります.
ここでは、DATETIME 2の異なる精度値を使用して、異なる時間フォーマットを表示する方法を示します.
DECLARE @D0 datetime2(0) = '2008-05-10 06:59:11.1234567';
DECLARE @D1 datetime2(1) = '2008-05-10 06:59:11.1234567';
DECLARE @D2 datetime2(2) = '2008-05-10 06:59:11.1234567';
DECLARE @D3 datetime2(3) = '2008-05-10 06:59:11.1234567';
DECLARE @D4 datetime2(4) = '2008-05-10 06:59:11.1234567';
DECLARE @D5 datetime2(5) = '2008-05-10 06:59:11.1234567';
DECLARE @D6 datetime2(6) = '2008-05-10 06:59:11.1234567';
DECLARE @D7 datetime2(7) = '2008-05-10 06:59:11.1234567';
PRINT @D0;
PRINT @D1;
PRINT @D2;
PRINT @D3;
PRINT @D4;
PRINT @D5;
PRINT @D6;
PRINT @D7;
上記のコードを実行すると、次の結果が得られます.
2008-05-10 06:59:11
2008-05-10 06:59:11.1
2008-05-10 06:59:11.12
2008-05-10 06:59:11.123
2008-05-10 06:59:11.1235
2008-05-10 06:59:11.12346
2008-05-10 06:59:11.123457
2008-05-10 06:59:11.1234567
ここでは、異なる数の精度で異なるマイクロ秒が表示され、DECLARE文でデフォルト値を設定したときにDATETIME(4)、DATETIME(5)、DATETIME(6)がいくつか切り捨てられたことに注意してください.
DATETIME 2を使用すると、日付と時間が1列にある場合により多くの選択肢が得られます.現在、日付と秒までの正確な時間を保存したい場合は、追加のコードを作成したり、高価なディスクオーバーヘッドを浪費したりすることなく、DATETIME(0)を使用してこのニーズを達成することができます.
日付だけを保存して時間がないと思ったことがありますか?もしそうであれば、古い日付データ型では完了できません.SQL Server 2008では、DATEデータ型を使用できます.DATEデータ型では、1つの日付値しか保存できません.サポートされている日付範囲は0001-01-01から9999-12-31までです.DATEデータ型のディスクのオーバーヘッドは3バイトしかかかりません.日付値を保存するだけで時間がない場合は、DATEを使用するとSMALLDATETIMEより1バイトのディスク領域を節約できます.
ここでは、DATEの値を表示する方法の例を示します.
上記のコードを実行すると、次の結果が得られます.
ここでPRINT文の表示日はYYYY-MM-DD形式を使用していますが、SELECT文ではCONVERT関数を使用してDATE変数をYYYY-MM-DD形式に変換する必要があります.ご覧のように、SELECTを使用してDATE変数を1つだけ表示すると、最後の出力結果には時間部分が含まれます.この機能は、すべての新しい日付と時刻のデータ型で一致しています.
TIME SQL Server 2008には、時間なしで時間値を1つだけ保存できるTIMEデータ型があります.これは、特定の日付に関係なく特定の時間情報を格納する場合に便利です.TIMEデータ型ストレージは24時間制で、タイムゾーンに関心がなく、100ナノ秒までの精度をサポートしています.TIMEデータ型は、DATETIME 2形式のように0～7の異なる精度をサポートし、ディスクのオーバーヘッドは精度に応じて3～5バイトです.TIME列の長さと精度は次の表の通りです.
ここでは、異なるDATETIME 2精度値を使用して、異なる時間フォーマットを表示する方法の例を示します.
DECLARE @T0 time(0) = '16:59:11.1234567';
DECLARE @T1 time(1) = '16:59:11.1234567';
DECLARE @T2 time(2) = '16:59:11.1234567';
DECLARE @T3 time(3) = '16:59:11.1234567';
DECLARE @T4 time(4) = '16:59:11.1234567';
DECLARE @T5 time(5) = '16:59:11.1234567';
DECLARE @T6 time(6) = '16:59:11.1234567';
DECLARE @T7 time(7) = '16:59:11.1234567';
PRINT @T0;
PRINT @T1;
PRINT @T2;
PRINT @T3;
PRINT @T4;
PRINT @T5;
PRINT @T6;
PRINT @T7;
上記のコードを実行すると、次の結果が得られます.
16:59:11
16:59:11.1
16:59:11.12
16:59:11.123
16:59:11.1235
16:59:11.12346
16:59:11.123457
16:59:11.1234567
なお,TIME(4),TIME(5),TIME(6)は表示時に精度カットを行い,我々が前に見たDATETIME 2の挙動と一致した.時間セクションのみを格納するTIMEデータ型列では、SQL Serverの時間値を検証して、有効な時間を確保し、スペースを節約できます.
DATETIMEOFFSETの最後の新しい日付データ型は、DATETIMEOFFSETデータ型です.このデータ型により、保存された日付と時間(24時間制)がタイムゾーンに一致します.時間部分は、DATETIME 2およびTIMEデータ型のような100ナノ秒までの精度をサポートすることができる.DATETIMEOFFSETでは、次の表に示すように、定義した時間部分の精度に依存して、8～10バイトのディスク領域オーバーヘッドが必要です.
タイムゾーン一致とは、タイムゾーン識別子がDATETIMEOFFSET列に格納されていることを意味し、タイムゾーン識別子は[-|+]hh:mm指定を表し、有効なタイムゾーン範囲は-14:00から+14:00までであり、この値はUTCを増加または減算してローカル時間を取得する.時間の変換については、「日付と時間の使用」トピックのオンライン叢書を参照してください.
More Date and Time ChoicesはSQL Server 2008を使用しており、日付と時間のデータを格納するためにより多くのデータ型が選択されています.DATEデータ型を使用して1つの日付のみを格納するか、TIMEデータ型を使用して1つの時間値のみを格納することができます.これらの新しいデータ型では、時間部分でサポートされている精度は100ナノ秒に達することができます.SQL Serverのタイムゾーンと一致する日付を保存する必要がある場合は、DATETIMEOFFSETデータ型を使用します.これらの新しい日付/時間データ型があれば、カスタムコードを大量に書く必要がなく、日付を適切なフォーマットで保存するための良いソリューションを見つけることができます.
古い日付データ型のレビュー
SQL Server 2008の新しい日付データ型について説明する前に、SQL Server 2005およびそれより古いバージョンで提供されている2つの日付データ型をレビューします.これらの古いデータ型はDATETIMEとSMALLDATETIMEです.この2つのデータ型はSQL Server 2008で使用できます.
DATETIMEデータ型ストレージは、1753年1月1日から9999年12月31日までの日付と時間部分を1つの単列値として格納します.時間部分の精度は3.33ミリ秒で、8バイトのストレージスペースが必要です.SMALLDATETIMEデータ型は、DATETIMEタイプよりもストレージ容量が少なく、4バイトしか必要ありませんが、日付と時間の範囲が小さくサポートされています.SMALLDATETIMEは、1900年1月1日から2079年6月6日までの日付のみをサポートし、時間部分は分までしか正確にできません.
この2つの限られた日付フォーマットは、より早い日付とより高い精度の時間を格納する際の選択を制限しています.これらの不足点は、SQL Server 2008の新しい日付データ型よりも、日付と時間を格納する際に多くの選択肢があるため、異なる日付と時間範囲をサポートする他のデータ構造からのデータ移行が困難です.
DATITIME2
DATETIME 2データ型はDATETIMEデータ型に対する拡張であり、この新しいデータ型は日付を許容できる範囲を拡張し、日付/時間値の時間部分に追加精度を追加する.DATETIME 2列のサポート日は0001-01-01から9999-01-01までです.拡張された日付範囲では、創造的な符号化アルゴリズムを使用することなく、より早い日付を格納できます.
DATETIME 2の時間部分の精度は、DATETIME 2列をどのように定義するかに依存します.時間部分は、時間、分、秒のみの時間値を格納することができます.または、異なる精密な決定でマイクロ秒を格納することができます.最大7桁の小数点以下で、微妙に100ナノ秒まで下に正確にすることができます.
DATETIME 2データ型を使用すると、19(YYY-MM-DD hh:mm:ss)から27(YYYY-MM-DD hh:mm:ss)まで、異なる長さの文字を使用して日付を格納および表示できます.これは、異なる数の精度ストレージに依存して、一定のディスクオーバーヘッドが必要です.DATETIME 2列に異なる精度を格納する場合は、列を定義するときに長さを指定する必要があります.DATETIME 2(X)のように精度を指定できます.ここで、Xは精度の長さを表し、0から7を表すことができます.表示長さと記憶領域の関係を次の表に示します.
上記の表に示すように、DATETIME 2列に日付/時刻値を格納するディスク領域のオーバーヘッドは、時間精度をどのくらい維持するかによって6～8バイトです.
DATETIME 2(3)フォーマットは、旧バージョンのSQL Serverで使用したDATETIMEフォーマットと同じですが、DATETIME 2(3)を使用すると精度が1ミリ秒までサポートされ、旧バージョンのDATETIMEフォーマットは3.33ミリ秒までしかサポートされません.DATETIME 2(3)は7バイトのディスクオーバーヘッドのみで、DATETIMEフォーマットは8バイト必要です.これにより、以前にリリースされたSQL Serverと同じ日付と時間値を格納でき、ディスク領域を節約できます.
DATETIME(7)フォーマットを使用すると、精度が向上したにもかかわらず、1つのテーブルの時間値が一意であることは保証されません.DATETIME(7)フォーマットを使用すると、同じ時間の異なる2つのレコードが同じテーブルに格納される可能性があります.
ここでは、DATETIME 2の異なる精度値を使用して、異なる時間フォーマットを表示する方法を示します.
DECLARE @D0 datetime2(0) = '2008-05-10 06:59:11.1234567';
DECLARE @D1 datetime2(1) = '2008-05-10 06:59:11.1234567';
DECLARE @D2 datetime2(2) = '2008-05-10 06:59:11.1234567';
DECLARE @D3 datetime2(3) = '2008-05-10 06:59:11.1234567';
DECLARE @D4 datetime2(4) = '2008-05-10 06:59:11.1234567';
DECLARE @D5 datetime2(5) = '2008-05-10 06:59:11.1234567';
DECLARE @D6 datetime2(6) = '2008-05-10 06:59:11.1234567';
DECLARE @D7 datetime2(7) = '2008-05-10 06:59:11.1234567';
PRINT @D0;
PRINT @D1;
PRINT @D2;
PRINT @D3;
PRINT @D4;
PRINT @D5;
PRINT @D6;
PRINT @D7;
上記のコードを実行すると、次の結果が得られます.
2008-05-10 06:59:11
2008-05-10 06:59:11.1
2008-05-10 06:59:11.12
2008-05-10 06:59:11.123
2008-05-10 06:59:11.1235
2008-05-10 06:59:11.12346
2008-05-10 06:59:11.123457
2008-05-10 06:59:11.1234567
ここでは、異なる数の精度で異なるマイクロ秒が表示され、DECLARE文でデフォルト値を設定したときにDATETIME(4)、DATETIME(5)、DATETIME(6)がいくつか切り捨てられたことに注意してください.
DATETIME 2を使用すると、日付と時間が1列にある場合により多くの選択肢が得られます.現在、日付と秒までの正確な時間を保存したい場合は、追加のコードを作成したり、高価なディスクオーバーヘッドを浪費したりすることなく、DATETIME(0)を使用してこのニーズを達成することができます.
日付だけを保存して時間がないと思ったことがありますか?もしそうであれば、古い日付データ型では完了できません.SQL Server 2008では、DATEデータ型を使用できます.DATEデータ型では、1つの日付値しか保存できません.サポートされている日付範囲は0001-01-01から9999-12-31までです.DATEデータ型のディスクのオーバーヘッドは3バイトしかかかりません.日付値を保存するだけで時間がない場合は、DATEを使用するとSMALLDATETIMEより1バイトのディスク領域を節約できます.
ここでは、DATEの値を表示する方法の例を示します.
SET NOCOUNT ON;
DECLARE @D DATE = '2001-03-1';
PRINT @D;
SELECT CONVERT(char(10),@D,120) DATEONLY, @D [DATE-N-TIME];
上記のコードを実行すると、次の結果が得られます.
2001-03-01
DATEONLY DATE-N-TIME
---------- -----------------------
2001-03-01 2001-03-01 00:00:00.000
ここでPRINT文の表示日はYYYY-MM-DD形式を使用していますが、SELECT文ではCONVERT関数を使用してDATE変数をYYYY-MM-DD形式に変換する必要があります.ご覧のように、SELECTを使用してDATE変数を1つだけ表示すると、最後の出力結果には時間部分が含まれます.この機能は、すべての新しい日付と時刻のデータ型で一致しています.
TIME SQL Server 2008には、時間なしで時間値を1つだけ保存できるTIMEデータ型があります.これは、特定の日付に関係なく特定の時間情報を格納する場合に便利です.TIMEデータ型ストレージは24時間制で、タイムゾーンに関心がなく、100ナノ秒までの精度をサポートしています.TIMEデータ型は、DATETIME 2形式のように0～7の異なる精度をサポートし、ディスクのオーバーヘッドは精度に応じて3～5バイトです.TIME列の長さと精度は次の表の通りです.
ここでは、異なるDATETIME 2精度値を使用して、異なる時間フォーマットを表示する方法の例を示します.
DECLARE @T0 time(0) = '16:59:11.1234567';
DECLARE @T1 time(1) = '16:59:11.1234567';
DECLARE @T2 time(2) = '16:59:11.1234567';
DECLARE @T3 time(3) = '16:59:11.1234567';
DECLARE @T4 time(4) = '16:59:11.1234567';
DECLARE @T5 time(5) = '16:59:11.1234567';
DECLARE @T6 time(6) = '16:59:11.1234567';
DECLARE @T7 time(7) = '16:59:11.1234567';
PRINT @T0;
PRINT @T1;
PRINT @T2;
PRINT @T3;
PRINT @T4;
PRINT @T5;
PRINT @T6;
PRINT @T7;
上記のコードを実行すると、次の結果が得られます.
16:59:11
16:59:11.1
16:59:11.12
16:59:11.123
16:59:11.1235
16:59:11.12346
16:59:11.123457
16:59:11.1234567
なお,TIME(4),TIME(5),TIME(6)は表示時に精度カットを行い,我々が前に見たDATETIME 2の挙動と一致した.時間セクションのみを格納するTIMEデータ型列では、SQL Serverの時間値を検証して、有効な時間を確保し、スペースを節約できます.
DATETIMEOFFSETの最後の新しい日付データ型は、DATETIMEOFFSETデータ型です.このデータ型により、保存された日付と時間(24時間制)がタイムゾーンに一致します.時間部分は、DATETIME 2およびTIMEデータ型のような100ナノ秒までの精度をサポートすることができる.DATETIMEOFFSETでは、次の表に示すように、定義した時間部分の精度に依存して、8～10バイトのディスク領域オーバーヘッドが必要です.
タイムゾーン一致とは、タイムゾーン識別子がDATETIMEOFFSET列に格納されていることを意味し、タイムゾーン識別子は[-|+]hh:mm指定を表し、有効なタイムゾーン範囲は-14:00から+14:00までであり、この値はUTCを増加または減算してローカル時間を取得する.時間の変換については、「日付と時間の使用」トピックのオンライン叢書を参照してください.
More Date and Time ChoicesはSQL Server 2008を使用しており、日付と時間のデータを格納するためにより多くのデータ型が選択されています.DATEデータ型を使用して1つの日付のみを格納するか、TIMEデータ型を使用して1つの時間値のみを格納することができます.これらの新しいデータ型では、時間部分でサポートされている精度は100ナノ秒に達することができます.SQL Serverのタイムゾーンと一致する日付を保存する必要がある場合は、DATETIMEOFFSETデータ型を使用します.これらの新しい日付/時間データ型があれば、カスタムコードを大量に書く必要がなく、日付を適切なフォーマットで保存するための良いソリューションを見つけることができます.