日付と時刻のタイプ
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 |
SQL モードでそのような使用が許可されている場合、無効なDATE 、 DATETIME 、 TIMESTAMPの値は、対応するタイプのゼロ値 ( '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」です。 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 タイム ゾーンに変換します。 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
);
NOT NULLとして指定されない限り、 DATETIMEのデフォルト値は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)を使用します。ここで、type_nameはTIME、DATETIME、またはTIMESTAMPです。例えば、CREATE TABLE t1 (t TIME(3), dt DATETIME(6));fsp0 ～ 6 の範囲でなければなりません。0小数部がないことを意味します。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またはTIMESTAMPDATEに変換すると、時間と小数部分は切り捨てられます。たとえば、「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 桁の年の部分
date に含まれる 2 桁の年の部分は、実際の年を明示的に示しておらず、曖昧です。
DATETIME 、 DATE 、およびTIMESTAMPタイプの場合、TiDB は曖昧さを排除するために次のルールに従います。
- 01 ～ 69 の値は 2001 ～ 2069 の値に変換されます。
- 70 ～ 99 の値は 1970 ～ 1999 年の値に変換されます。
これらのルールは、1 つの例外を除いてYEARタイプにも適用されます。
数値00をYEAR(4)に挿入すると、結果は 2000 ではなく 0000 になります。
結果を 2000 にしたい場合は、値を 2000 に指定します。
2 桁の年の部分は、 MIN()やMAX()などの一部の関数では正しく計算されない可能性があります。これらの関数には、4 桁の形式の方が適しています。