日付と時刻
概要
DATE はカレンダー値のみを保持し、TIMESTAMP は内部的には UTC で保存されますが、現在のセッションのタイムゾーンを通して表示されます。TIMESTAMP_TZ は、監査やレプリケーションのシナリオで必要となる元のオフセットを保持します。
例
DATE
CREATE TABLE events (event_date DATE);
INSERT INTO events VALUES ('2024-01-15'), ('2024-12-31');
SELECT * FROM events;
結果:
┌────────────┐
│ event_date │
├────────────┤
│ 2024-01-15 │
│ 2024-12-31 │
└────────────┘
TIMESTAMP
CREATE TABLE meetings (
meeting_id INT,
meeting_time TIMESTAMP
);
INSERT INTO meetings VALUES (1, '2024-01-15 14:00:00+08:00');
SETTINGS (timezone = 'UTC')
SELECT meeting_id, meeting_time FROM meetings;
SETTINGS (timezone = 'America/New_York')
SELECT meeting_id, meeting_time FROM meetings;
結果 (timezone = 'UTC'):
┌────────────┬──────────────────────┐
│ meeting_id │ meeting_time │
├────────────┼──────────────────────┤
│ 1 │ 2024-01-15T06:00:00 │
└────────────┴──────────────────────┘
結果 (timezone = 'America/New_York'):
┌────────────┬──────────────────────┐
│ meeting_id │ meeting_time │
├────────────┼──────────────────────┤
│ 1 │ 2024-01-15T01:00:00 │
└────────────┴──────────────────────┘
TIMESTAMP_TZ
CREATE TABLE system_logs (
log_id INT,
log_time TIMESTAMP_TZ
);
INSERT INTO system_logs VALUES
(1, '2024-01-15 14:00:00+08:00'),
(2, '2024-01-15 06:00:00+00:00'),
(3, '2024-01-15 01:00:00-05:00');
SETTINGS (timezone = 'UTC')
SELECT log_id, TO_STRING(log_time) AS log_time FROM system_logs;
SETTINGS (timezone = 'Asia/Shanghai')
SELECT log_id, TO_STRING(log_time) AS log_time FROM system_logs;
結果 (timezone = 'UTC'):
┌────────┬────────────────────────────────────────────┐
│ log_id │ log_time │
├────────┼────────────────────────────────────────────┤
│ 1 │ 2024-01-15 14:00:00.000000 +0800 │
│ 2 │ 2024-01-15 06:00:00.000000 +0000 │
│ 3 │ 2024-01-15 01:00:00.000000 -0500 │
└────────┴────────────────────────────────────────────┘
結果 (timezone = 'Asia/Shanghai'):
┌────────┬────────────────────────────────────────────┐
│ log_id │ log_time │
├────────┼────────────────────────────────────────────┤
│ 1 │ 2024-01-15 14:00:00.000000 +0800 │
│ 2 │ 2024-01-15 06:00:00.000000 +0000 │
│ 3 │ 2024-01-15 01:00:00.000000 -0500 │
└────────┴────────────────────────────────────────────┘
オフセットは保存される値の一部であるため、表示は変化しません。
適切な型の選択
- 時刻を含まないカレンダー値には
DATEを使用します。 - 異なるセッションで同じ時点をそれぞれのローカルタイムゾーンで表示する必要がある場合は、
TIMESTAMPを使用します。 - コンプライアンス対応やデバッグのために入力時のオフセットを保持する必要がある場合は、
TIMESTAMP_TZを使用します。
夏時間調整
enable_dst_hour_fix を有効にすると、夏時間によってその日の一部の時間がスキップされる場合に、TiDB Cloud Lake は存在しない時刻を自動的に次の有効な時刻へ繰り上げます。
SET enable_dst_hour_fix = 1;
SETTINGS (timezone = 'America/Toronto')
SELECT to_datetime('2024-03-10 02:01:00');
結果:
┌────────────────────────────────────┐
│ to_datetime('2024-03-10 02:01:00') │
├────────────────────────────────────┤
│ 2024-03-10T03:01:00 │
└────────────────────────────────────┘
存在しない時刻に対してエラーを返すデフォルトの動作に戻したい場合は、SET enable_dst_hour_fix = 0 を使用します。
無効な値の処理
サポート範囲外の日付は、自動的にその最小値に丸められます。
SELECT
ADD_DAYS(TO_DATE('9999-12-31'), 1) AS overflow_date,
SUBTRACT_MINUTES(TO_DATE('1000-01-01'), 1) AS underflow_timestamp;
結果:
┌───────────────┬──────────────────────────┐
│ overflow_date │ underflow_timestamp │
├───────────────┼──────────────────────────┤
│ 0001-01-01 │ 0999-12-31T18:41:28 │
└───────────────┴──────────────────────────┘
エラーを返す代わりに、値は表現可能な最小の日付またはタイムスタンプに折り返されます。
日付と時刻の書式設定
TO_DATE や TO_TIMESTAMP などの関数は、明示的な書式文字列を受け付けます。date_format_style と week_start を調整することで、値の解析方法や表示方法を制御できます。
日付フォーマットスタイル
date_format_style を使用すると、2 つのフォーマット語彙を切り替えられます。
- MySQL（デフォルト）は
%Y、%m、%dのような指定子を使用します。 - Oracle は ANSI スタイルのマスクに合わせて、
YYYY、MM、DDのような指定子を使用します。
-- Oracle-style mask
SETTINGS (date_format_style = 'Oracle')
SELECT to_string('2024-04-05'::DATE, 'YYYY-MM-DD');
結果（Oracle）:
┌──────────────────────────────────────┐
│ to_string('2024-04-05'::DATE, 'YYYY-MM-DD') │
├──────────────────────────────────────┤
│ 2024-04-05 │
└──────────────────────────────────────┘
-- Back to MySQL-style mask
SETTINGS (date_format_style = 'MySQL')
SELECT to_string('2024-04-05'::DATE, '%Y-%m-%d');
結果（MySQL）:
┌──────────────────────────────────────┐
│ to_string('2024-04-05'::DATE, '%Y-%m-%d') │
├──────────────────────────────────────┤
│ 2024-04-05 │
└──────────────────────────────────────┘
週の開始日の設定
week_start は、WEEK 精度を使用する際に、DATE_TRUNC や TRUNC などの関数で週の開始日をどの曜日にするかを定義します。
SETTINGS (week_start = 0) SELECT DATE_TRUNC(WEEK, to_date('2024-04-05')); -- Sunday
SETTINGS (week_start = 1) SELECT DATE_TRUNC(WEEK, to_date('2024-04-05')); -- Monday
結果（week_start = 0）:
┌────────────────────────────────┐
│ DATE_TRUNC(WEEK, TO_DATE('2024-04-05')) │
├────────────────────────────────┤
│ 2024-03-31 │
└────────────────────────────────┘
結果（week_start = 1）:
┌────────────────────────────────┐
│ DATE_TRUNC(WEEK, TO_DATE('2024-04-05')) │
├────────────────────────────────┤
│ 2024-04-01 │
└────────────────────────────────┘
MySQL フォーマット指定子
日付と時刻のフォーマットを処理するために、TiDB Cloud Lake は chrono::format::strftime モジュールを使用します。これは Rust の chrono ライブラリが提供する標準モジュールです。このモジュールにより、日付と時刻のフォーマットを精密に制御できます。以下の内容は https://docs.rs/chrono/latest/chrono/format/strftime/index.html からの抜粋です。
数値指定子 %? のデフォルトの埋め込み動作は上書きできます。これは他の指定子では許可されておらず、BAD_FORMAT エラーになります。
%C, %y: これは切り捨て除算です。そのため、紀元前 100 年（年番号 -99）はそれぞれ -1 と 99 と表示されます。
%U: 週 1 はその年の最初の日曜日から始まります。最初の日曜日より前の日は週 0 になる可能性があります。
%G, %g, %V: 週 1 は、その年に少なくとも 4 日含まれる最初の週です。週 0 は存在しないため、これらは %G または %g と一緒に使用する必要があります。
%S: うるう秒を考慮するため、60 になることがあります。
%f, %.f, %.3f, %.6f, %.9f, %3f, %6f, %9f:
デフォルトの %f は右寄せで、glibc などとの互換性のため常に 9 桁にゼロ埋めされます。そのため、常に直前の整数秒からのナノ秒数を表します。たとえば、直前の秒から 7ms 後は 007000000 と表示され、7000000 を解析しても同じ結果になります。
バリアント %.f は左寄せで、精度に応じて 0、3、6、9 桁の小数部を出力します。たとえば、直前の秒から 70ms 後を %.f で表すと .070 と表示されます（注: .07 ではありません）。また、.07 や .070000 などを解析しても同じ結果になります。小数部が 0 の場合、または次の文字が . でない場合は、何も出力または読み取りしないことがあります。
バリアント %.3f、%.6f、%.9f は左寄せで、f の前の数字に応じて 3、6、9 桁の小数部を出力します。たとえば、直前の秒から 70ms 後を %.3f で表すと .070 と表示されます（注: .07 ではありません）。また、.07 や .070000 などを解析しても同じ結果になります。小数部が 0 の場合、または次の文字が . でない場合は何も読み取らないことがありますが、出力時には指定された長さで表示されます。
バリアント %3f、%6f、%9f は左寄せで、f の前の数字に応じて 3、6、9 桁の小数部を出力しますが、先頭のドットは付きません。たとえば、直前の秒から 70ms 後を %3f で表すと 070 と表示されます（注: 07 ではありません）。また、07 や 070000 などを解析しても同じ結果になります。小数部が 0 の場合は、何も読み取らないことがあります。
%Z: 解析されたデータからオフセットは設定されず、検証もされません。タイムゾーンは完全に無視されます。これは glibc の strptime におけるこのフォーマットコードの扱いに似ています。
省略形からオフセットへ確実に変換できません。たとえば、CDT は Central Daylight Time（北米）を意味する場合もあれば、China Daylight Time を意味する場合もあります。
%+: %Y-%m-%dT%H:%M:%S%.f%:z と同じです。つまり、秒の小数部は 0、3、6、9 桁で、タイムゾーンオフセットにはコロンが入ります。
この形式では、%:z の代わりに Z または UTC を使用することもサポートしています。これらは +00:00 と等価です。
なお、T、Z、UTC はすべて大文字小文字を区別せずに解析されます。
一般的な strftime 実装では、この指定子に対して異なる形式（かつロケール依存）を使用します。Chrono における %+ の形式ははるかに安定していますが、出力を厳密に制御したい場合は、この指定子の使用を避けるのが最善です。
%s: これは埋め込みされず、負の値になることがあります。Chrono の目的上、これはうるう秒以外の秒のみを考慮するため、ISO C strftime の動作とはわずかに異なります。
Oracle フォーマット指定子
date_format_style が 'Oracle' に設定されている場合、以下のフォーマット指定子がサポートされます。
同じデータを使用した MySQL と Oracle のフォーマットスタイルの比較例を以下に示します。
-- MySQL format style (default)
SELECT to_string('2022-12-25'::DATE, '%m/%d/%Y');
┌────────────────────────────────┐
│ to_string('2022-12-25', '%m/%d/%Y') │
├────────────────────────────────┤
│ 12/25/2022 │
└────────────────────────────────┘
-- Oracle format style (same data as MySQL example above)
SETTINGS (date_format_style = 'Oracle')
SELECT to_string('2022-12-25'::DATE, 'MM/DD/YYYY');
┌────────────────────────────────┐
│ to_string('2022-12-25', 'MM/DD/YYYY') │
├────────────────────────────────┤
│ 12/25/2022 │
└────────────────────────────────┘