mysqlの日付と時刻の型

時刻の値を表す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 モードが有効な場合、これらの値を使用すると警告が生成されることに注意してください。

列の型「ゼロ」値

DATETIME '0000-00-00 00:00:00'

DATE '0000-00-00'

TIMESTAMP 00000000000000

TIME '00:00:00'

年0000

· 「ゼロ」値は特別な値ですが、表に示す値を使用して明示的に保存または参照できます。値「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 カラム タイプのプロパティは、このセクションで説明されているものとは多くの点で大きく異なります。古い TIMESTAMP データを MySQL 5.1 で動作するように変換する必要がある場合、詳細については MySQL 4.1 リファレンス マニュアルを参照してください。

TIMESTAMP列の表示形式はDATETIME列と同じです。つまり、表示幅は 19 文字に固定され、形式は YYYY-MM-DD HH:MM:SS となります。

MySQL サーバーは MAXDB モードでも実行できます。サーバーがこのモードで実行されている場合、TIMESTAMP は DATETIME と等しくなります。つまり、テーブルの作成時にサーバーが MAXDB モードで実行されている場合、TIMESTAMP 列は DATETIME 列として作成されます。その結果、列は DATETIME 表示形式を使用し、同じ値の範囲を持ち、現在の日付と時刻で自動的に初期化または更新されません。

MAXDB モードを有効にするには、サーバーの起動時に --sql-mode=MAXDB サーバー オプションを使用するか、グローバル sql_mode 変数を設定して実行時に SQL サーバー モードを MAXDB に設定します:

mysql> SET GLOBAL sql_mode=MAXDB；

クライアントは次のようにサーバーを有効にできます。接続には MAXDB モードで実行します:

mysql> SET SESSION sql_mode=MAXDB;

MySQL は、日または月の列にゼロを含むタイムスタンプ値、または不正な日付値を含むタイムスタンプ値を受け入れません。このルールの唯一の例外は、特別な値「0000-00-00 00:00:00」です。

TIMESTAMP をいつ初期化して更新するか、またどの列を初期化して更新するかを非常に柔軟に決定できます:

· 現在のタイムスタンプをデフォルト値および自動的に更新される値として指定できます。ただし、どちらか一方のみを選択することも、どちらも選択しないこともできます。 (ある列で 1 つの動作を選択し、別の列で別の動作を選択することはできません)。

· どの TIMESTAMP 列を自動的に初期化するか、現在の日時に更新するかを指定できます。 1 番目の TIMESTAMP 列は必要なくなりました。

以下で説明する情報は、MAXDB モードを有効にせずに作成されたテーブルの TIMESTAMP 列にのみ適用されることに注意してください。 (前述したように、MAXDB モードでは列が DATETIME 列として作成されます)。 TIMESTAMP 列の初期化と更新を制御するルールは次のとおりです:

· テーブル内の最初の TIMESTAMP 列が DEFAULT 値として指定されている場合、それを無視することはできません。 デフォルト値は CURRENT_TIMESTAMP または定数の日付と時刻の値です。

・DEFAULT NULLは、1番目のTIMESTAMP列のDEFAULT CURRENT_TIMESTAMPと同じです。他の TIMESTAMP 列の場合、DEFAULT NULL は DEFAULT 0 として扱われます。

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

· CREATE TABLE ステートメントでは、次のいずれかの方法で最初の TIMESTAMP 列を宣言できます:

o DEFAULT CURRENT_TIMESTAMP 句と ON UPDATE CURRENT_TIMESTAMP 句を使用して、現在のタイムスタンプをデフォルト値として使用し、自動的に更新します。

o DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP と同様に、DEFAULT または ON UPDATE 句を使用しません。

o ON UPDATE 句の代わりに DEFAULT CURRENT_TIMESTAMP 句を使用します。列は現在のタイムスタンプをデフォルト値として使用しますが、自動的には更新されません。

o DEFAULT 句を使用せず、ON UPDATE CURRENT_TIMESTAMP 句を使用すると、列のデフォルト値は 0 になり、自動的に更新されます。

o 定数 DEFAULT 値を使用します。デフォルト値は列に示されています。列に ON UPDATE CURRENT_TIMESTAMP 句がある場合は自動的に更新されますが、それ以外の場合は更新されません。

言い換えると、初期値と自動的に更新される値に現在のタイムスタンプを使用することも、どちらか一方を使用することも、どちらも使用しないこともできます。 (たとえば、ON UPDATE を指定すると、列を自動的に初期化せずに自動更新を有効にすることができます)。

· DEFAULT 句と ON UPDATE 句では、CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()、または NOW() を使用できます。それらはすべて同じ効果があります。

2 つのプロパティの順序は重要ではありません。 DEFAULT と ON UPDATE の両方が TIMESTAMP 列に指定されている場合は、どちらかを他方よりも優先できます。

例、次のステートメントは同等です:

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);

· 列 1 以外の TIMESTAMP 列の自動デフォルトまたは更新を指定するには、最初の TIMESTAMP 列に定数 DEFAULT 値を明示的に割り当てて自動初期化を無効にし、更新する必要があります。 (たとえば、DEFAULT 0 または DEFAULT '2003-01-01 00:00:00')。次に、他の TIMESTAMP 列のルールは、DEFAULT 句と ON UPDATE 句を無視できないことを除いて、最初の TIMESTAMP 列の場合と同じです。これを行うと、初期化や更新は自動的には行われません。

例: 次のステートメントは同等です:

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以YYYY格式检索和显示YEAR值。范围是1901到2155。

可以指定各种格式的YEAR值：

· 四位字符串，范围为'1901'到'2155'。

· 四位数字，范围为1901到2155。

· 两位字符串，范围为'00'到'99'。'00'到'69'和'70'到'99'范围的值被转换为2000到2069和1970到1999范围的YEAR值。

· 两位整数，范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串'0'或'00'或它被解释为0000。

· 函数返回的结果，其值适合YEAR上下文，例如NOW()。

非法YEAR值被转换为0000。

4. Y2K事宜和日期类型

MySQL本身对于2000年(Y2K)是安全的，但输入给MySQL的值可能不安全。任何包含两位年值的输入都会令人模糊，因为世纪不知道。这些值必须解释为四位形式，因为MySQL内部使用四位来保存年。

对于DATETIME、DATE、TIMESTAMP和YEAR类型，MySQL使用以下规则解释含模糊年值的日期：

· 00-69范围的年值转换为2000-2069。

· 70-99范围的年值转换为1970-1999。

请记住这些规则只是合理猜测数据值表示什么。如果MySQL使用的启发不能产生正确的值，你应提供包含四位年值的确切输入。

ORDER BY可以正确排序有两位年的TIMESTAMP或YEAR值。

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