日付と時刻の種類
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」です。 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は、次の形式規則に従います。
- 1901 から 2155 までの 4 桁の数字の範囲
- 「1901」から「2155」までの 4 桁の文字列の範囲
- 1 桁または 2 桁の数字の範囲は 1 ～ 99 です。したがって、1 ～ 69 は 2001 ～ 2069 年に変換され、70 ～ 99 は 1970 ～ 1999 年に変換されます。
- '0' から '99' までの 1 桁または 2 桁の文字列
- 値 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)を使用して、小数精度をサポートする列を定義しますtype_nameはTIME、DATETIMEまたはTIMESTAMPです。例えば、CREATE TABLE t1 (t TIME(3), dt DATETIME(6));fspは 0 から 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に変換されると、時間部分 '00:00:00' が追加されます。これは、DATE に時間情報が含まれていないためです。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 桁の年部分
date に含まれる 2 桁の年部分は、実際の年を明示的に示しておらず、あいまいです。
DATETIME 、 DATE 、およびTIMESTAMP型の場合、TiDB は次の規則に従ってあいまいさを排除します。
- 01 ～ 69 の値は 2001 ～ 2069 の値に変換されます
- 70 ～ 99 の値は 1970 ～ 1999 の値に変換されます
これらの規則はYEAR型にも適用されますが、例外が 1 つあります。
YEAR(4)に数字の00を挿入すると、結果は 2000 ではなく 0000 になります。
結果を 2000 にする場合は、値を 2000 に指定します。
MIN()やMAX()などの一部の関数では、2 桁の年の部分が正しく計算されない場合があります。これらの関数については、4 桁の形式の方が適しています。