0Pricing
SQL Interview Prep · レッスン

タイムゾーンとタイムスタンプ

UTCでの保存やタイムゾーン変換、面接でよく問われるタイムスタンプの注意点を学びます。

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

なぜタイムゾーンで候補者がつまずくのか

タイムゾーンは、自信のある候補者でもつまずきやすい分野です。そのため面接官は、理解の深さを確認するために質問します。核心となる問いは常に、「地域をまたいでタイムスタンプをどのように保存し、比較しますか」です。

実務的な答えは、特定の関数ではなく運用上の原則です。すべてを UTC で保存し、表示時の端でのみ変換します。保存モデルを正しく設計すれば、ほとんどのクエリは簡単になります。

  • timestamp と timestamptz
  • タイムゾーン間の変換
  • 真の基準としての UTC

timestamp と timestamptz

PostgreSQL には2種類のタイムスタンプ型があり、これを混同するのは面接でよくあるミスです。

  • timestamp（タイムゾーンなし）：タイムゾーンが付いていない壁時計の時刻です。指定した値がそのまま保存されます。
  • timestamptz（タイムゾーン付き）：内部では UTC として保存されます。入力時にはセッションのタイムゾーンから変換され、出力時には元のタイムゾーンに変換されます。

名前に反して、timestamptz はタイムゾーンを保存しません。UTC における正確な時点を保存します。この点まで説明できると、面接官に好印象を与えられます。

CREATE TABLE events (
  id          bigint,
  occurred_at timestamptz   -- recommended: an absolute instant
);

UTC で保存し、端で変換する

黄金律です。時点は UTC で保存し（timestamptz を使います）、ユーザーに表示するときだけ現地のタイムゾーンに変換します。これにより、夏時間への切り替えに伴う曖昧さを避けられ、どの地域でも時刻順に正しく並べられます。

「なぜ UTC なのですか」と聞かれたら、次のように答えてください。UTC には夏時間による変化がないため、同じ壁時計の時刻が2回現れたり、存在しなくなったりすることがありません。これは現地時刻とは異なります。

-- Display a UTC instant in a user's zone (Postgres)
SELECT occurred_at AT TIME ZONE 'America/New_York' AS local_time
FROM events;

AT TIME ZONE の二つの意味

AT TIME ZONE は入力の型によって正反対の動作をするため、巧妙であり、頻出する落とし穴でもあります。

  • timestamptz に適用すると、絶対的な時点をそのタイムゾーンへ変換し、通常の timestamp（その場所の時計上の時刻）を返します。
  • 通常の timestamp に適用すると、その時計上の時刻がそのタイムゾーンの時刻であると解釈し、timestamptz を返します。

どちらの方向に動作するのかを把握することが、すべてのポイントです。

-- timestamptz -> local wall clock (returns timestamp)
SELECT TIMESTAMPTZ '2024-03-01 12:00:00+00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 21:00:00

-- plain timestamp interpreted in a zone (returns timestamptz)
SELECT TIMESTAMP '2024-03-01 12:00:00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 03:00:00+00

現在の時点を取得する

「now」関数の動作を把握しておきましょう。Postgres の NOW() と CURRENT_TIMESTAMP は timestamptz を返します。これらが返すのは文の実行開始時刻ではなくトランザクションの開始時刻であり、長時間のトランザクションではこの違いが重要になります。

UTC を明示的に使う場合は、NOW() AT TIME ZONE 'UTC' のように変換します。MySQL では、UTC_TIMESTAMP() を使うと UTC を直接取得できます。

SELECT
  NOW()                       AS tx_start_tz,
  NOW() AT TIME ZONE 'UTC'    AS utc_walltime;

夏時間が本当の難敵です

面接官は DST の境界ケースを好んで質問します。時計を進めるときは、現地の時計上の1時間が存在しなくなり、時計を戻すときは1時間が繰り返されます。現地時刻を保存すると、このような時刻が曖昧になったり、無効になったりします。

UTC を保存すれば、この問題を完全に回避できます。すべての時点が一意で、単調に増加するためです。'America/New_York' のような地域名（-05:00 のような固定オフセットではありません）を指定すれば、データベースが任意の日付に対して DST の規則を正しく適用できます。

-- Region name applies DST automatically for the given date
SELECT TIMESTAMPTZ '2024-07-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS summer, -- EDT (-04)
       TIMESTAMPTZ '2024-01-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS winter; -- EST (-05)

タイムゾーンをまたいで現地の日単位でグループ化する

現実的な問題として、「各ユーザーの現地時刻で日次アクティブユーザーを集計する」ケースを考えます。UTC のタイムスタンプを直接切り捨てると、UTC 以外のユーザーでは深夜の境界が正しくなりません。

日単位に切り捨てる前に、ユーザーのタイムゾーンへ変換してください。変換によって時計上の時刻がずれるため、日付の境界が現地のものと一致します。

SELECT
  DATE_TRUNC('day', occurred_at AT TIME ZONE u.tz) AS local_day,
  COUNT(DISTINCT e.user_id)                         AS dau
FROM events e
JOIN users u ON u.id = e.user_id
GROUP BY 1
ORDER BY 1;

タイムスタンプを安全に比較する

timestamptz 型のカラムで絞り込む場合は、明示的な時点と比較してください。できれば UTC のリテラル、またはオフセット付きの timestamptz を使います。単なる文字列と比較すると、予測できないセッションのタイムゾーンで解釈される可能性があります。

これにより、誰がクエリを実行しても比較結果が曖昧になりません。

SELECT *
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2024-03-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2024-04-01 00:00:00+00';

エポックと Unix タイムスタンプ

多くのシステムでは、Unix エポック（1970-01-01 UTC からの経過秒数）として時刻を保存します。面接では、整数型のカラムを渡され、その値を読み取るよう求められることがあります。

  • Postgres: TO_TIMESTAMP(epoch_seconds) は timestamptz を返します。
  • エポックへ戻す場合: EXTRACT(EPOCH FROM occurred_at)。
  • MySQL: FROM_UNIXTIME() と UNIX_TIMESTAMP()。

エポック値は本質的に UTC であるため、保存形式として広く使われています。

SELECT
  TO_TIMESTAMP(1709294400)               AS as_ts,   -- from epoch
  EXTRACT(EPOCH FROM NOW())::bigint        AS as_epoch; -- to epoch

異なる SQL 方言でのタイムゾーンに関する注意点

どの環境でも詳しい知識があるように見せるための早見表を示します。

  • Postgres: timestamptz と AT TIME ZONE を使い、最も充実したサポートを備えています。
  • MySQL: TIMESTAMP はセッションの time_zone を介して自動変換されます。CONVERT_TZ(t, from, to) で明示的に変換できます。DATETIME はタイムゾーンを認識しません。
  • SQL Server: datetimeoffset はオフセットを保存します。AT TIME ZONE 'name' は Windows のタイムゾーン名を使って変換します。
-- MySQL explicit conversion
SELECT CONVERT_TZ(event_dt, 'UTC', 'Europe/Istanbul') AS local_dt
FROM events;

詳しい例: 深夜をまたぐセッション

微妙なレポート要件として、セッションが深夜をまたぐ場合に、現地の暦日ごとにセッション数を集計するケースがあります。ここでも原則は同じで、現地時刻に変換してからバケット化します。

開始時刻と終了時刻は timestamptz として保存し、レポートでは変換後の開始時刻から現地の日付を導出します。セッションを2日間に分割する必要がある場合は、日付の連続表（day spine）に結合します。これは追加で提案するとよいポイントです。

SELECT
  DATE_TRUNC('day', started_at AT TIME ZONE 'Europe/Istanbul') AS local_day,
  COUNT(*) AS sessions
FROM sessions
GROUP BY 1
ORDER BY 1;

クイックチェック

推奨される保存戦略と、その理由を確認しましょう。

まとめ: タイムゾーンとタイムスタンプ

覚えておくべき原則は次のとおりです。

  • timestamptz としてUTC を保存し、表示時だけ名前付きのタイムゾーンへ変換します。
  • timestamptz が保存するのは UTC の時点であり、名前に反してタイムゾーンではありません。
  • AT TIME ZONE は入力の型によって両方向に動作します。timestamptz を現地の時計上の時刻へ変換することも、通常の timestamp をあるタイムゾーンの時刻として解釈することもできます。
  • DST が自動的に適用されるように、'America/New_York' のような地域名を使い、固定オフセットは避けます。
  • 日単位に切り捨てる前に現地時刻へ変換し、カラムは明示的な UTC の時点と比較します。

よくある質問

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

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

「タイムゾーンとタイムスタンプ」で何を学びますか？

UTCでの保存やタイムゾーン変換、面接でよく問われるタイムスタンプの注意点を学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

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

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

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

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

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

  1. 日付の計算とインターバル
  2. 日付の切り捨てとバケット化
  3. 文字列の解析とフォーマット
  4. タイムゾーンとタイムスタンプ
← SQL Interview Prepに戻る