MySQL の基本チュートリアル 2 — データ型: 日付と時刻の型

時刻の値を表すDATE型と時刻型は、DATETIME、DATE、TIMESTAMP、TIME、YEARです。各 time タイプには、有効な値の範囲と、MySQL が表現できない不正な値を指定するときに使用される「ゼロ」値があります。 TIMESTAMP型は後述する独自の自動更新機能を備えています。

不正な日付を挿入しようとすると、MySQL は警告またはエラーを出します。 ALLOW_INVALID_DATES SQL モードを使用すると、MySQL が「1999-11-31」などの特定の日付を受け入れることができます。ユーザーが指定した「間違っている可能性がある」値を、今後の処理のためにデータベース (Web フォームなど) に保存したい場合に便利です。このモードでは、MySQL は月の範囲が 0 ～ 12 であり、日の範囲が 0 ～ 31 であることのみを検証します。 MySQL では日/月、および DATE または DATETIME カラムで日が 0 である日付を保存できるため、これらの範囲にはゼロを含めることができます。これは、正確な日付がわからない誕生日をアプリケーションで保存する必要がある場合に便利です。この場合は、日付を「1999-00-00」または「1999-01-00」として保存します。 DATE_SUB() や DATE_ADD などの完全な日付を必要とする関数では、そのような日付を保存すると正しい結果が得られません。 (日付にゼロを表示したくない場合は、NO_ZERO_IN_DATESQL モードを使用できます)。

MySQL では、「0000-00-00」を「疑似日付」として保存することもできます (NO_ZERO_DATE SQL モードが使用されていない場合)。場合によっては、NULL 値を使用するよりもこの方が便利です (データとインデックスが占有するスペースが少なくなります)。

MySQL でサポートしたい日付の種類をより明確に知るには、sql_mode システム変数を対応するモード値に設定します。

日付と時刻の型を扱うときは、次の点に留意する必要があります:

· MySQL は、指定された日付または時刻の型の値を標準出力形式で取得しますが、さまざまな入力値形式を解釈するために最善を尽くします。指定する場合 (たとえば、日付型または時刻型に割り当てられる値、または日付型または時刻型と比較される値を指定するとき)。次のセクションで説明する形式のみがサポートされます。有効な値を入力してください。他の形式の値を使用すると、予期しない結果が発生する可能性があります。

· 2 桁の年の値が含まれる日付は、世紀が不明なため混乱を招きます。 MySQL は、次のルールを使用して 2 桁の年の値を解釈します:

o 70 ～ 99 の範囲の年の値は、1970 ～ 1999 に変換されます。

o 00〜69の範囲の年数値は2000〜2069に変換されます。

· MySQL は値をいくつかの形式で解釈しようとしますが、日付は、そのままの月-日-年または日-月ではなく、常に年-月-日の順序になります (例: '98-09-04')。他の場所で一般的に使用される - 年順 (例: '09-04-98'、'04-09-98')。

· MySQL は、値が数値コンテキストで使用されている場合、日付または時刻型の値を数値に自動的に変換し、その逆も同様です。

· MySQL は、(このセクションの冒頭で説明したように) 範囲外またはその型にとって不正な日付または時刻型の値を検出すると、その値をそのクラスの「ゼロ」値に変換します。 1 つの例外は、範囲外の TIME 値が TIME 範囲の対応するエンドポイントにクリップされることです。

以下の表は、さまざまな「ゼロ」値の形式を示しています。 NO_ZERO_DATE SQL モードが有効な場合、これらの値を使用すると警告が生成されることに注意してください。

· 「ゼロ」値は特別な値ですが、表に示す値を使用して明示的に保存または参照できます。値「0」または 0 を使用して保存または参照することもでき、この方が書き込みが容易です。

· MyODBC で使用される「ゼロ」の日付または時刻の値は、ODBC がそのような値を処理できないため、MyODBC 2.50.12 以降では自動的に NULL に変換されます。

1. DATETIME、DATE、および TIMESTAMP 型

• 1. MySQL 4.1 以降の TIMESTAMP 属性

DATETIME、DATE、および TIMESTAMP 型は関連しています。このセクションでは、それらの特徴、類似点、相違点について説明します。

日付と時刻の両方の情報を含む値が必要な場合は、DATETIME 型を使用します。 MySQL は、DATETIME 値を「YYYY-MM-DD HH:MM:SS」形式で取得して表示します。サポートされている範囲は「1000-01-01 00:00:00」から「9999-12-31 23:59:59」です。 (「サポートされている」とは、以前の値が機能する可能性はあるものの、保証はないことを意味します)。

DATE 型は、時刻部分を含まずに日付値のみが必要な場合に使用する必要があります。 MySQL は、「YYYY-MM-DD」形式を使用して DATE 値を取得および表示します。サポートされている範囲は「1000-01-01」から「9999-12-31」です。

TIMESTAMP カラムタイプのプロパティは固定されておらず、MySQL のバージョンとサーバーが実行されている SQL モードに依存します。これらのプロパティについては、このセクションで後ほど説明します。

DATETIME、DATE、TIMESTAMP の値は、一般的な形式:

· 'YYYY-MM-DD HH:MM:SS' または 'YY-MM-DD HH:MM:SS' 形式の文字列を使用して指定できます。 「緩和された」構文が許可されます。日付部分または時刻部分の間の区切り文字として、任意の句読点文字を使用できます。たとえば、「98-12-31 11:30:45」、「98.12.31 11+30+45」、「98/12/31 11*30*45」、「98@12@31 11^30^」などです。 45'' は同等です。

· 「YYYY-MM-DD」または「YY-MM-DD」形式の文字列。ここでは「緩和された」構文も許可されます。たとえば、「98-12-31」、「98.12.31」、「98/12/31」、および「98@12@31」は同等です。

· 区切り文字のない「YYYYMMDDHHMMSS」または「YYMMDDHHMMSS」形式の文字列。文字列が日付型に対して意味があると想定されます。たとえば、「19970523091528」と「970523091528」は「1997-05-23 09:15:28」として解釈されますが、「971122129015」は不正であり（意味のない議事録部分がある）、「0000- 00-00 00」になります。 :00:00'。

· 区切り文字のない「YYYYMMDD」または「YYMMDD」形式の文字列。文字列が日付型に対して意味があると想定されます。たとえば、「19970523」と「970523」は「1997-05-23」と解釈されますが、「971332」は不正（意味のない月と日の部分がある）であり、「0000-00-00」になります。

· YYYYMMDDHHMMSS または YYMMDDHHMMSS 形式の数値 (数値が日付タイプとして意味があると仮定)。たとえば、19830905132800 と 830905132800 は「1983-09-05 13:28:00」として解釈されます。

· YYYYMMDD または YYMMDD 形式の数値 (数値が日付タイプとして意味があると仮定)。たとえば、19830905 と 830905 は「1983-09-05」として解釈されます。

· 関数によって返される結果には、NOW() や CURRENT_DATE など、DATETIME、DATE、または TIMESTAMP コンテキストに適した値が含まれます。

無効な DATETIME、DATE、または TIMESTAMP 値は、対応するタイプの「ゼロ」値 ('0000-00-00 00:00:00'、'0000-00-00' または 00000000000000) に変換されます。

日付部分の区切り文字を含む文字列値の場合、日と月の値が10未満の場合は、2桁を指定する必要はありません。 「1979-6-9」は「1979-06-09」と同じです。同様に、時刻部分の区切り文字を含む文字列値の場合、時、分、秒の値が 10 未満の場合は、2 桁を指定する必要はありません。 「1979-10-30 1:2:3」は「1979-10-30 01:02:03」と同じです。

数値の長さは 6、8、12、または 14 桁である必要があります。数値の長さが 8 桁または 14 桁の場合、最初の 4 桁が年を表す YYYYMMDD または YYYYMMDDHHMMSS 形式であるとみなされます。数値の長さが 6 桁または 12 桁の場合、最初の 2 桁が年を表す YYMMDD または YYMMDDHHMMSS 形式であるとみなされます。他の数値は、最も近い長さにゼロが埋め込まれたものとして解釈されます。

非修飾子文字列として指定された値は、指定された長さを使用して解釈されます。文字列の長さが 8 文字または 14 文字の場合、最初の 4 桁は年を表します。それ以外の場合、最初の 2 桁は年を表します。文字列内の各出現箇所を左から右に解釈して、年、月、日、時、分、秒の値を見つけます。これは、6 文字より短い文字列を使用すべきではないことを意味します。たとえば、1999 年 3 月を意味すると思って '9903' を指定すると、MySQL はテーブルに「ゼロ」の日付値を挿入します。これは、年と月の値が 99 と 03 ですが、日の部分が完全に欠落しているため、この値は有効な日付ではないためです。ただし、欠落している月または日の部分を表すゼロ値を明示的に指定できます。たとえば、「990300」を使用して値「1999-03-00」を挿入できます。

ある日付型の値を別の日付型に割り当てることは、ある程度可能です。ただし、値は変更されるか、一部の情報が失われる可能性があります:

· DATETIME または TIMESTAMP オブジェクトに DATE 値を割り当てる場合、DATE 値は変更されないため、結果の値の時刻部分は '00:00:00' に設定されます。時間情報が含まれています。

· DATETIME または TIMESTAMP 値を DATE オブジェクトに代入すると、DATE 値には時間情報が含まれないため、結果の値の時間部分が削除されます。

· DATETIME、DATE、TIMESTAMP の値は同じ形式を使用して指定できますが、値の種類によって範囲が異なることに注意してください。たとえば、TIMESTAMP 値を 1970 より前または 2037 より後にすることはできません。これは、「1968-01-01」などの日付は、DATETIME または DATE 値には有効ですが、TIMESTAMP 値には無効であり、そのようなオブジェクトに割り当てられた場合は 0 に変換されることを示します。

日付値を指定するときは、いくつかの落とし穴に注意してください:

· 文字列として指定された値に許可されている非厳密な形式は、欺瞞的である可能性があります。たとえば、値「10:11:12」は「:」区切り文字により時刻値のように見えますが、日付コンテキストで使用される場合、値は年「2010-11-12」として解釈されます。 「45」は法定月ではないため、値「10:45:15」は「0000-00-00」に変換されます。

· 非厳密モードでは、MySQL サーバーは日付の有効性に関する基本的なチェックのみを実行します。年、月、日の範囲はそれぞれ 1000 ～ 9999、00 ～ 12、00 ～ 31 です。これらの範囲外の部分を含む日付は「0000-00-00」に変換されます。 「2002-04-31」などの不正な日付を保存することは依然として許可されていることに注意してください。厳密モードを使用していない場合に日付が有効であることを確認するには、アプリケーションを確認する必要があります。

厳密モードでは、不正な日付は受け入れられず、変換されません。

· 2 桁の年の値が含まれる日付は、世紀が不明なため混乱を招きます。 MySQL は、次のルールを使用して 2 桁の年の値を解釈します。00 ～ 69 の範囲の年の値は、2000 ～ 2069 に変換されます。

o 70 ～ 99 の範囲の年次値は 1970 ～ 1999 年に変換されます。

1.1. MySQL 4.1 以降の TIMESTAMP 属性

MySQL 4.1 リファレンス マニュアル を参照してください。 TIMESTAMP列の表示形式はDATETIME列と同じです。つまり、表示幅は 19 文字に固定され、形式は YYYY-MM-DD HH:MM:SS となります。

mysql> SET GLOBAL sql_mode=MAXDB；

mysql> SET SESSION sql_mode=MAXDB;

TIMESTAMP

· テーブル内の TIMESTAMP 列は、現在のタイムスタンプに自動的に初期化されたり、更新されるように設定できます。

o 用常量DEFAULT值，列有给出的 默认值。如果列有一个ON UPDATE CURRENT_TIMESTAMP子句，它自动更新，否则不。

换句话说，你可以为初始值和自动更新的值使用当前的时间戳，或者其中一个使用，或者两个皆不使用。(例如，你可以指定ON UPDATE来启用自动更新而不让列自动初始化）。

· 在DEFAULT和ON UPDATE子句中可以使用CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()或者NOW()。它们均具有相同的效果。

两个属性的顺序并不重要。如果一个TIMESTAMP列同时指定了DEFAULT和ON UPDATE，任何一个可以在另一个的前面。

例子，下面这些语句是等效的：

CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                             DEFAULT CURRENT_TIMESTAMP);

· 要为TIMESTAMP列而不是第1列指定自动默认或更新，必须通过将第1个TIMESTAMP列显式分配一个常量DEFAULT值来禁用自动初始化和更新。(例如，DEFAULT 0或DEFAULT'2003-01-01 00:00:00')。然后，对于其它TIMESTAMP列，规则与第1个TIMESTAMP列相同，例外情况是不能忽略DEFAULT和ON UPDATE子句。如果这样做，则不会自动进行初始化或更新。

例如：下面这些语句是等效的：

CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                  DEFAULT CURRENT_TIMESTAMP);

可以对每个连接设置当前的时区。TIMESTAMP值以UTC格式保存，存储时对当前的时区进行转换，检索时再转换回当前的时区。只要时区设定值为常量，便可以得到保存时的值。如果保存一个TIMESTAMP值，应更改时区然后检索该值，它与你保存的值不同。这是因为在两个方向的转换中没有使用相同的时区。当前的时区可以用作time_zone系统变量的值。

可以在TIMESTAMP列的定义中包括NULL属性以允许列包含NULL值。例如：

CREATE TABLE t
(
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT 0,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

如果未指定NULL属性，将列设置为NULL设置则会将它设置为当前的时间戳。请注意允许NULL值的TIMESTAMP列不会采用当前的时间戳，除非要么其 默认值定义为CURRENT_TIMESTAMP，或者NOW()或CURRENT_TIMESTAMP被插入到该列内。换句话说，只有使用如下定义创建，定义为 NULL的TIMESTAMP列才会自动更新：

CREATE TABLE t (ts NULLDEFAULT CURRENT_TIMESTAMP)；

否则-也就是说，如果使用NULL而不是DEFAULT TIMESTAMP来定义TIMESTAMP列，如下所示...

CREATE TABLE t1 (ts NULL DEFAULT NULL);
CREATE TABLE t2 (ts NULL DEFAULT '0000-00-00 00:00:00');

...则必须显式插入一个对应当前日期和时间的值。例如：

INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);

2. TIME类型

MySQL以'HH:MM:SS'格式检索和显示TIME值(或对于大的小时值采用'HHH:MM:SS'格式)。TIME值的范围可以从'-838:59:59'到'838:59:59'。小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。

你可以用各种格式指定TIME值：

· 'D HH:MM:SS.fraction'格式的字符串。还可以使用下面任何一种“非严格”语法：'HH:MM:SS.fraction'、'HH:MM:SS'、'HH:MM'、'D HH:MM:SS'、'D HH:MM'、'D HH'或'SS'。这里D表示日，可以取0到34之间的值。请注意MySQL还不保存分数。

· 'HHMMSS'格式的没有间割符的字符串，假定是有意义的时间。例如，'101112'被理解为'10:11:12'，但'109712'是不合法的(它有一个没有意义的分钟部分)，将变为'00:00:00'。

· HHMMSS格式的数值，假定是有意义的时间。例如，101112被理解为'10:11:12'。下面格式也可以理解：SS、MMSS、HHMMSS、HHMMSS.fraction。请注意MySQL还不保存分数。

· 函数返回的结果，其值适合TIME上下文，例如CURRENT_TIME。

对于指定为包括时间部分间割符的字符串的TIME值，如果时、分或者秒值小于10，则不需要指定两位数。'8:3:2'与'08:03:02'相同。

为TIME列分配简写值时应注意。没有冒号，MySQL解释值时假定最右边的两位表示秒。(MySQL解释TIME值为过去的时间而不是当天的时间）。例如，你可能认为'1112'和1112表示'11:12:00'(11点过12分)，但MySQL将它们解释为'00:11:12'(11分，12 秒)。同样，'12'和12 被解释为 '00:00:12'。相反，TIME值中使用冒号则肯定被看作当天的时间。也就是说，'11:12'表示'11:12:00'，而不是'00:11:12'。

超出TIME范围但合法的值被裁为范围最接近的端点。例如，'-850:00:00'和'850:00:00'被转换为'-838:59:59'和'838:59:59'。

无效TIME值被转换为'00:00:00'。请注意由于'00:00:00'本身是一个合法TIME值，只从表内保存的一个'00:00:00'值还不能说出原来的值是 '00:00:00'还是不合法的值。

3. YEAR型

YEAR型は年を表す半角型です。

MySQL は、YEAR 値を YYYY 形式で取得して表示します。範囲は 1901 ～ 2155 です。

YEAR 値はさまざまな形式で指定できます:

· '1901' から '2155' までの 4 桁の文字列。

· 1901 から 2155 までの 4 桁。

· 「00」から「99」までの 2 桁の文字列。 「00」から「69」および「70」から「99」の範囲の値は、2000から2069および1970から1999の範囲のYEAR値に変換されます。

・1〜99の範囲の2桁の整数。1〜69および70〜99の範囲の値は、2001〜2069および1970〜1999の範囲のYEAR値に変換されます。 2 桁の整数の範囲は、ゼロを数値として直接指定して 2000 として解釈することができないという点で、2 桁の文字列の範囲とは若干異なることに注意してください。文字列「0」または「00」として指定する必要があります。指定しない場合は、0000 として解釈されます。

· NOW() など、関数によって返される結果。その値は YEAR コンテキストに適しています。

不正な YEAR 値は 0000 に変換されます。

4. Y2K 問題と日付型

MySQL 自体は 2000 年 (Y2K) に関しては安全ですが、MySQL に入力された値は安全ではない可能性があります。 2 桁の年の値を含む入力は、世紀が不明であるためあいまいになります。 MySQL は内部で年を格納するために 4 桁を使用するため、これらの値は 4 桁として解釈する必要があります。

DATETIME、DATE、TIMESTAMP、YEAR 型の場合、MySQL は次のルールを使用して、あいまいな年の値を持つ日付を解釈します:

· 00 ～ 69 の範囲の年の値は 2000 ～ 2069 に変換されます。

· 70 ～ 99 の範囲の年次値は 1970 ～ 1999 年に変換されます。

これらのルールは、データ値が何を表すかについての合理的な推測にすぎないことを覚えておいてください。 MySQL で使用されるヒューリスティックが正しい値を生成しない場合は、4 桁の年の値を含む正確な入力を提供する必要があります。

ORDER BY は、2 桁の年を使用して TIMESTAMP または YEAR 値を正しく並べ替えることができます。

MIN() や MAX() などの一部の関数は、TIMESTAMP または YEAR を数値に変換します。これは、これらの関数が 2 桁の年の値を持つ値では正しく機能しないことを意味します。この場合の修正方法は、TIMESTAMP または YEAR を 4 桁の年の形式に変換するか、MIN(DATE_ADD(TIMESTAMP,INTERVAL 0 DAYS)) を使用することです。

上記は、MySQL 基本チュートリアル 2 - データ型の日付と時刻の型の内容です。さらに関連する内容については、PHP 中国語 Web サイト (m.sbmmt.com) に注目してください。