SQL Academy · レッスン

タイムゾーン：TIMESTAMPとTIMESTAMPTZ

TIMESTAMPとTIMESTAMPTZの違いを理解し、AT TIME ZONEで変換し、移植性のあるアプリケーションのためにUTCで保存します。

レッスン 3/413 ステップ

「タイムゾーン：TIMESTAMPとTIMESTAMPTZ」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

2種類のタイムスタンプ

PostgreSQLには、タイムスタンプの型が2つあります。

TIMESTAMP — 「素の」タイムスタンプで、タイムゾーン情報を持ちません
TIMESTAMPTZ — タイムゾーン付きタイムスタンプ（内部ではUTCとして保存されます）

目安：イベントにはTIMESTAMPTZを、純粋に抽象的な時刻、たとえば「営業時間の午前9時」にはTIMESTAMPを使用してください。

TIMESTAMPTZの仕組み

内部では常にUTCとして保存されます。INSERT時には、入力値がセッションのTZからUTCに変換されます。SELECT時には、出力値がセッションのTZに戻されます。

SET TIME ZONE 'Europe/Berlin';
INSERT INTO events (ts) VALUES ('2024-03-15 12:00:00');
-- stored as 2024-03-15 11:00:00 UTC (Berlin is UTC+1 in winter)

SET TIME ZONE 'UTC';
SELECT ts FROM events;
-- 2024-03-15 11:00:00+00

TIMESTAMPは情報が失われる

通常のTIMESTAMPはタイムゾーンを認識しません。「2024-03-15 12:00:00」は、どの地域の現地時刻を意味する可能性もあります。ユーザーがタイムゾーンを移動すると、本来の意味が失われます。

AT TIME ZONE

表示用にタイムゾーン間で変換します。

SELECT ts AT TIME ZONE 'America/New_York' AS ny_time FROM events;
SELECT ts AT TIME ZONE 'Asia/Tokyo'        AS tokyo_time FROM events;

UTCで保存し、現地時刻で表示する

ベストプラクティスは、TIMESTAMPTZで保存し、SELECT時にユーザーのタイムゾーンへ変換することです。逆方向の変換は行わないでください。

SELECT id,
       ts AT TIME ZONE COALESCE(u.tz, 'UTC') AS local_ts
FROM events e
JOIN users u ON u.id = e.user_id;

SESSION TIME ZONE

セッション単位、ユーザー単位、またはグローバルに設定できます。

-- Session:
SET TIME ZONE 'Europe/Berlin';

-- Per-user (PG):
ALTER ROLE alice SET timezone TO 'Europe/Berlin';

-- Server default in postgresql.conf:
-- timezone = 'UTC'

リテラルにタイムゾーンを指定する

リテラルにタイムゾーンを埋め込みます。

SELECT TIMESTAMPTZ '2024-03-15 12:00:00 Europe/Berlin';
SELECT '2024-03-15 12:00:00+02'::TIMESTAMPTZ;

NOW()はTIMESTAMPTZを返す

NOW() と CURRENT_TIMESTAMP は常にTIMESTAMPTZを返します。これらをTIMESTAMP列と混在させると、バグの原因になることがよくあります。

TIMESTAMP（タイムゾーンなし）が適している場合

タイムゾーンに依存しない抽象的な時刻や現地時刻、たとえば「店舗は09:00に開店する」には、TIMESTAMP（またはTIME）を使用してください。

CREATE TABLE store_hours (
  store_id BIGINT,
  open_at  TIME NOT NULL,
  close_at TIME NOT NULL
);

DSTの切り替え

TIMESTAMPTZはDSTを自動的に処理します。INTERVALの加算は、DSTの境界をまたがない限り、時計上の時間として機能します。境界をまたぐ場合は、23時間または25時間ずれることがあります。

SELECT TIMESTAMPTZ '2024-03-30 23:00:00 Europe/Berlin' + INTERVAL '5 hours';
-- Result accounts for the DST transition on March 31

オフセットではなくタイムゾーン名を保存する

+02:00のようなオフセットだけでは、DSTがいつ始まるか分かりません。Europe/Berlinのようなタイムゾーン名なら分かります。ユーザーの居住地のタイムゾーンを保存する場合は、IANA名を保存してください。

振り返り

イベントには、基本的にTIMESTAMPTZを使用してください。

UTCとして保存し、セッションのTZで表示する
表示用の変換にはAT TIME ZONEを使用する
抽象的な現地時刻に限り、TIMESTAMP（タイムゾーンなし）を使用する

確認問題

グローバル向けのSaaSでユーザーイベントを記録しています。occurred_at 列にはどのタイムスタンプ型を使用すべきですか？

対話的なレッスンで答える

無料で開始

AI チューターと学ぶ SQL — 無料

ブラウザでリアルコードを書いて実行し、24/7 の AI チューターから瞬時にサポートを受け、ウェブまたはアプリで続きから学習できます。

コース: 46
レッスン: 183

無料で学習を始めるコース全体を表示

よくある質問

「タイムゾーン：TIMESTAMPとTIMESTAMPTZ」レッスンは無料ですか？

はい。「タイムゾーン：TIMESTAMPとTIMESTAMPTZ」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「タイムゾーン：TIMESTAMPとTIMESTAMPTZ」で何を学びますか？

TIMESTAMPとTIMESTAMPTZの違いを理解し、AT TIME ZONEで変換し、移植性のあるアプリケーションのためにUTCで保存します。ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン3/4です。

「タイムゾーン：TIMESTAMPとTIMESTAMPTZ」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Academyに戻る