TiDB Cloud
TiDB Self-Managed
Learning Center
お問い合わせ

日付と時刻の種類

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' ) に自動的に変換されます。

サポートされているタイプ

DATE型

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

DATE

TIME型

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型

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型

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

タイムゾーンの処理

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

YEAR型

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の自動初期化と更新

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

時間値の小数部分

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)

日付と時刻の型間の変換

場合によっては、日付型と時刻型の間で変換を行う必要があります。しかし、一部の変換では情報が失われることがあります。たとえば、 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桁の年の部分

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

DATETIME DATEタイプの場合、TiDB は曖昧さを排除するために次のルールTIMESTAMP従います。

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

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

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

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

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

このページは役に立ちましたか？