Skip to content

data-type-date-and-time.md

Latest commit

 

History

History
265 lines (183 loc) · 15.9 KB

data-type-date-and-time.md

File metadata and controls

265 lines (183 loc) · 15.9 KB
Raw
title Date and Time Types
summary サポートされている日付と時刻の種類について説明します。

日付と時刻の型 {#date-and-time-types}

TiDBは、時間値を格納するためにMySQLのすべての日付と時刻データ型（ DATE 、 TIME 、 DATETIME 、 TIMESTAMP 、 YEARをサポートしています。詳細については、 MySQLの日付と時刻のデータ型参照してください。

これらのタイプにはそれぞれ有効な値の範囲があり、無効な値を示すためにゼロ値が使用されます。さらに、タイプTIMESTAMPとタイプDATETIMEでは、変更時に新しい時刻値が自動的に生成されます。

日付と時刻の値の型を扱うときは、次の点に注意してください。

  • TiDB はさまざまな形式を解釈しようとしますが、日付部分は月-日-年や日-月-年ではなく、年-月-日 (たとえば、「1998-09-04」) の形式である必要があります。

  • 日付の年の部分が 2 桁で指定されている場合、TiDB はそれを特定のルールに基づいて変換します。

  • コンテキスト内で数値が必要な場合、TiDBは日付または時刻値を自動的に数値型に変換します。例:

    mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
+---------------------+----------------+--------------------+
| NOW()               | NOW()+0        | NOW(3)+0           |
+---------------------+----------------+--------------------+
| 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
+---------------------+----------------+--------------------+

  • TiDBは、無効な値やサポート範囲外の値を、その型のゼロ値に自動的に変換することがあります。この動作はSQLモードの設定に依存します。例：

    mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values ('2090-11-32:22:33:44');
ERROR 1292 (22007): Truncated incorrect time value: '2090-11-32:22:33:44'
mysql> set @@sql_mode='';                                                                                                                                                                                                                     Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values ('2090-11-32:22:33:44');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t1;
+----------+
| a        |
+----------+
| 00:00:00 |
+----------+
1 row in set (0.01 sec)

  • 異なる SQL モードを設定すると、TiDB の動作が変わる場合があります。

  • SQLモードNO_ZERO_DATEが有効になっていない場合、TiDBは列DATEとDATETIMEの月または日にゼロ値（例：「2009-00-00」または「2009-01-00」）を許可します。この日付型を関数（例：列DATE_SUB()またはDATE_ADD() ）で計算する場合、結果が不正確になる可能性があります。

  • デフォルトでは、TiDBはSQLモードNO_ZERO_DATE有効にします。このモードでは、「0000-00-00」などのゼロ値が保存されるのを防ぎます。

さまざまな種類のゼロ値を次の表に示します。

日付の種類 「ゼロ」値
日付 '0000-00-00'
時間 '00:00:00'
日時 '0000-00-00 00:00:00'
タイムスタンプ '0000-00-00 00:00:00'
年 0000

無効なDATE 、 DATETIME 、 TIMESTAMP値は、SQL モードで許可されている場合、対応するタイプのゼロ値 (「0000-00-00」または「0000-00-00 00:00:00」) に自動的に変換されます。

サポートされているタイプ {#supported-types}

DATE型 {#code-date-code-type}

DATE日付部分のみを含み、時刻部分は含みませんYYYY-MM-DD形式で表示されます。サポートされる範囲は「0000-01-01」から「9999-12-31」です。

DATE

TIME型 {#code-time-code-type}

TIME型の場合、フォーマットはHH:MM:SS[.fraction]で、有効な値の範囲は '-838:59:59.000000' から '838:59:59.000000' です。5 TIME 、1 日の時刻だけでなく、2 つのイベント間の時間間隔も示します。オプションで 0 から 6 の範囲のfsp値を指定し、小数秒の精度を指定できます。省略した場合、デフォルトの精度は 0 です。

TIME[(fsp)]

注記：

TIMEの省略形に注意してください。例えば、「11:12」は「00:11:12」ではなく「11:12:00」を意味します。一方、「1112」は「00:11:12」を意味します。これらの違いは、 :文字の有無によって生じます。

DATETIME型 {#code-datetime-code-type}

DATETIME日付部分と時刻部分の両方が含まれます。有効な値の範囲は「0000-01-01 00:00:00.000000」から「9999-12-31 23:59:59.999999」です。

TiDBはDATETIME値をYYYY-MM-DD HH:MM:SS[.fraction]形式で表示しますが、文字列または数値を使用してDATETIME列に値を代入できます。オプションで0から6の範囲のfsp値を指定して、小数秒の精度を指定できます。省略した場合、デフォルトの精度は0です。

DATETIME[(fsp)]

TIMESTAMP型 {#code-timestamp-code-type}

TIMESTAMP日付部分と時刻部分の両方を含みます。有効な値の範囲は、UTC時間で「1970-01-01 00:00:01.000000」から「2038-01-19 03:14:07.999999」までです。オプションで0から6の範囲のfsp値を指定して、小数秒の精度を指定できます。省略した場合、デフォルトの精度は0です。

TIMESTAMPでは、月部分または日部分にゼロを含めることはできません。唯一の例外は、ゼロ値自体（「0000-00-00 00:00:00」）です。

TIMESTAMP[(fsp)]

タイムゾーンの処理 {#timezone-handling}

TIMESTAMP保存する場合、TiDB はTIMESTAMP値を現在のタイムゾーンから UTC タイムゾーンに変換します。5 TIMESTAMP取得する場合、TiDB は保存されているTIMESTAMP値を UTC タイムゾーンから現在のタイムゾーンに変換します（注: DATETIMEこの方法では処理されません）。各接続のデフォルトのタイムゾーンはサーバーのローカルタイムゾーンですが、環境変数time_zoneで変更できます。

警告：

MySQLと同様に、 TIMESTAMPデータ型は2038年問題影響を受けます。2038を超える値を格納する場合は、代わりにDATETIME型の使用を検討してください。

YEAR型 {#code-year-code-type}

YEAR型は「YYYY」形式で指定します。サポートされる値の範囲は1901から2155まで、または0000です。

YEAR[(4)]

YEAR次の形式規則に従います。

  • 4桁の数字の範囲は1901年から2155年までです
  • 4桁の文字列の範囲は「1901」から「2155」までです
  • 1桁または2桁の数字の範囲は1から99です。したがって、1〜69は2001〜2069に変換され、70〜99は1970〜1999に変換されます。
  • 1桁または2桁の文字列の範囲は「0」から「99」までです
  • 値0は0000として扱われ、文字列「0」または「00」は2000として扱われます。

無効な値YEARは自動的に 0000 に変換されます (ユーザーがNO_ZERO_DATE SQL モードを使用していない場合)。

TIMESTAMPとDATETIMEの自動初期化と更新 {#automatic-initialization-and-update-of-code-timestamp-code-and-code-datetime-code}

TIMESTAMPまたはDATETIME値タイプを持つ列は、自動的に初期化されるか、現在の時刻に更新されます。

テーブル内のTIMESTAMPまたはDATETIME値タイプを持つ列に対して、デフォルト値または自動更新値を現在のタイムスタンプとして設定できます。

これらのプロパティは、列を定義する際にDEFAULT CURRENT_TIMESTAMPとON UPDATE CURRENT_TIMESTAMP設定することで設定できます。DEFAULT はDEFAULT 0やDEFAULT '2000-01-01 00:00:00'などの特定の値に設定することもできます。

CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

DATETIMEのデフォルト値は、 NOT NULLが指定されていない限りNULLです。後者の場合、デフォルト値が設定されていないと、デフォルト値は 0 になります。

CREATE TABLE t1 (
    dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP,         -- default NULL
    dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);

時間値の小数部分 {#decimal-part-of-time-value}

DATETIMEとTIMESTAMP値は、マイクロ秒単位の精度で最大 6 桁の小数部を持つことができますDATETIME型またはTIMESTAMP型の列では、小数部は破棄されずに保存されます。小数部がある場合、値は「YYYY-MM-DD HH:MM:SS[.fraction]」の形式で表され、小数部の範囲は 000000 から 999999 です。小数部と残りの部分を区切るために小数点を使用する必要があります。

  • 小数精度をサポートする列を定義するにはtype_name(fsp)使用します。3 type_name TIME 、 DATETIMEまたはTIMESTAMPになります。例えば、

    CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

    fsp 0 から 6 までの範囲でなければなりません。

    0小数部がないことを意味します。2 fsp省略した場合、デフォルトは0です。

  • 小数部を含むTIME 、 DATETIME 、またはTIMESTAMP挿入する場合、小数部の桁数が少なすぎる、または多すぎる場合は、四捨五入が必要になることがあります。例：

    mysql> CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO fractest VALUES
     > ('17:51:04.777', '2014-09-08 17:51:04.777',   '2014-09-08 17:51:04.777');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM fractest;
+-------------|------------------------|------------------------+
| c1          | c2                     | c3                     |
+-------------|------------------------|------------------------+
| 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.78 |
+-------------|------------------------|------------------------+
1 row in set (0.00 sec)

日付と時刻の型間の変換 {#conversions-between-date-and-time-types}

日付型と時刻型の間で変換が必要になる場合があります。しかし、変換によっては情報が失われる可能性があります。例えば、 DATE 、 DATETIME 、 TIMESTAMPという値はそれぞれ独自の範囲を持ちます。 TIMESTAMP UTC時間で1970年より前、またはUTC時間「2038-01-19 03:14:07」より後であってはなりません。このルールに基づくと、「1968-01-01」は有効な日付値であるDATEまたはDATETIMEですが、 TIMESTAMPに変換すると 0 になります。

DATEの変換:

  • DATE DATETIMEまたはTIMESTAMPに変換すると、DATEには時間情報が含まれていないため、時間部分「00:00:00」が追加されます。
  • DATE TIMEに変換すると、結果は「00:00:00」になります。

DATETIMEまたはTIMESTAMPの変換:

  • DATETIMEまたはTIMESTAMP DATEに変換する場合、時刻と小数部分は切り捨てられます。例えば、「1999-12-31 23:59:59.499」は「1999-12-31」に変換されます。
  • DATETIMEまたはTIMESTAMP TIMEに変換すると、 TIMEは日付情報が含まれていないため、日付部分は破棄されます。

TIME他の日時形式に変換すると、日付部分は自動的にCURRENT_DATE()に指定されます。最終的な変換結果は、 TIMEとCURRENT_DATE()で構成される日付になります。つまり、TIME の値が '00:00:00' から '23:59:59' の範囲外の場合、変換後の日付部分は現在の日付を示しません。

TIME DATEに変換する場合もプロセスは同様で、時間部分は破棄されます。

CAST()関数を使用すると、値をDATE型に明示的に変換できます。例:

date_col = CAST(datetime_col AS DATE)

TIMEとDATETIME数値形式に変換します。例:

mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;
+-----------|-------------|--------------+
| CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
+-----------|-------------|--------------+
| 09:28:00  |       92800 |    92800.887 |
+-----------|-------------|--------------+
mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
+---------------------|----------------|--------------------+
| NOW()               | NOW()+0        | NOW(3)+0           |
+---------------------|----------------|--------------------+
| 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
+---------------------|----------------|--------------------+

日付に含まれる2桁の年の部分 {#two-digit-year-portion-contained-in-the-date}

日付に含まれる 2 桁の年部分は実際の年を明示的に示しておらず、あいまいです。

DATETIME DATE TIMESTAMPの場合、TiDB は曖昧さを排除するために次の規則に従います。

  • 01から69までの値は2001から2069までの値に変換されます
  • 70から99までの値は1970から1999までの値に変換されます

これらのルールはYEARタイプにも適用されますが、1 つの例外があります。

数字の00 YEAR(4)に代入すると、結果は 2000 ではなく 0000 になります。

結果を 2000 にしたい場合は、値を 2000 に指定します。

MIN()やMAX()の一部の関数では、2桁の年部分が正しく計算されない場合があります。これらの関数では、4桁の形式の方が適しています。