今すぐわかる！PostgreSQL date_trunc 関数の使い方・入門ガイド

はじめに：なぜ `date_trunc` 関数が重要なのか？

リレーショナルデータベース、特にPostgreSQLを扱う上で、日付や時刻のデータは非常に頻繁に利用されます。顧客の注文時間、センサーがデータを記録した時刻、ユーザーがログインした時間など、あらゆる情報が時間軸と結びついています。これらの生の日時データを分析する際、特定の粒度でデータを扱う必要が出てきます。例えば、「今日1日の売上合計」や「今週のウェブサイトアクセス数」、「先月の新規登録者数」を知りたい場合です。

しかし、データベースに記録されている時間は、通常、秒やミリ秒といった非常に細かい精度を持っています。例えば、「2023-10-27 10:35:12.123456」のようなデータです。これをそのまま利用して「今日1日の売上」を集計しようとしても、「2023-10-27 10:00:00」と「2023-10-27 11:00:00」のデータは、時刻部分が異なるため、別々のものとして扱われてしまいます。必要なのは、時刻部分を無視して日付だけでデータをまとめる、つまり「切り捨てる」処理です。

ここで登場するのがPostgreSQLの強力な関数 date_trunc です。date_trunc 関数は、指定した日時データに対して、特定の時間単位（年、月、日、時間、分、秒など）で切り捨てを行う機能を提供します。これにより、複雑な日付・時刻計算を行うことなく、簡単にデータを集計・分析可能な形式に変換できます。

date_trunc は、集計、レポート作成、時系列分析、データクレンジングなど、様々な場面でその真価を発揮します。このガイドでは、date_trunc 関数の基本的な使い方から、様々な応用例、内部的な仕組み、そして他の関連関数との比較まで、詳細かつ網羅的に解説します。これを読めば、あなたも今日から date_trunc を自信を持って使いこなせるようになるでしょう。

`date_trunc` 関数の基本構文

date_trunc 関数の基本的な構文は非常にシンプルです。

sql date_trunc(field, source [, time_zone ])

この関数は、2つまたは3つの引数を取ります。

field: 必須。どの時間単位で切り捨てを行うかを指定する文字列です。例えば 'year', 'month', 'day', 'hour' などがあります。指定できる値については後述します。
source: 必須。切り捨てを行う対象となる日時データです。TIMESTAMP WITH TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, DATE, TIME, INTERVAL などのデータ型を受け付けます。
time_zone: オプション。source がタイムゾーン情報を持たない TIMESTAMP WITHOUT TIME ZONE の場合、このタイムゾーンで解釈してから切り捨てを行います。source が TIMESTAMP WITH TIME ZONE の場合は、そのタイムゾーン情報が優先されるため、この引数は無視されるか、または指定しても効果がありません。通常はタイムゾーン名 (例: 'Asia/Tokyo', 'UTC') を指定します。

date_trunc 関数は、指定された source の時間を、指定された field の単位で切り捨てた結果を返します。戻り値のデータ型は、通常、source 引数のデータ型と同じになります（ただし、source が DATE の場合は TIMESTAMP を返します）。

`field` 引数で指定できる値とその意味

field 引数には、切り捨ての粒度を指定する文字列を渡します。指定できる主な値とその意味は以下の通りです。

microsecond: マイクロ秒以下の部分を切り捨てます。実質的には何も切り捨てません（PostgreSQLのデフォルト精度はマイクロ秒のため）。
millisecond: ミリ秒以下の部分を切り捨てます。
- 例: SELECT date_trunc('millisecond', '2023-10-27 10:35:12.123456'::timestamp); -> 2023-10-27 10:35:12.123
second: 秒以下の部分（ミリ秒、マイクロ秒）を切り捨てます。
- 例: SELECT date_trunc('second', '2023-10-27 10:35:12.123456'::timestamp); -> 2023-10-27 10:35:12
minute: 分以下の部分（秒、ミリ秒、マイクロ秒）を切り捨てます。
- 例: SELECT date_trunc('minute', '2023-10-27 10:35:12.123456'::timestamp); -> 2023-10-27 10:35:00
hour: 時間以下の部分（分、秒、ミリ秒、マイクロ秒）を切り捨てます。
- 例: SELECT date_trunc('hour', '2023-10-27 10:35:12.123456'::timestamp); -> 2023-10-27 10:00:00
day: 日以下の部分（時間、分、秒、ミリ秒、マイクロ秒）を切り捨てます。これにより、その日の始まり（真夜中0時0分0秒）が得られます。
- 例: SELECT date_trunc('day', '2023-10-27 10:35:12.123456'::timestamp); -> 2023-10-27 00:00:00
week: 包含する週の始まりの日付（通常は月曜日）に切り捨てます。これは、特定の週の最初の日の真夜中0時0分0秒になります。ISO 8601週番号付けシステムに従います。
- 例: 2023年10月27日は金曜日です。その週の始まりは10月23日月曜日です。
- SELECT date_trunc('week', '2023-10-27 10:35:12.123456'::timestamp); -> 2023-10-23 00:00:00
month: 月以下の部分（日、時間など）を切り捨てます。これにより、その月の1日の真夜中0時0分0秒が得られます。
- 例: SELECT date_trunc('month', '2023-10-27 10:35:12.123456'::timestamp); -> 2023-10-01 00:00:00
quarter: 四半期以下の部分を切り捨てます。これにより、その四半期の最初の月（1月、4月、7月、10月）の1日の真夜中0時0分0秒が得られます。
- 例: 2023年10月は第4四半期です。第4四半期の始まりは10月1日です。
- SELECT date_trunc('quarter', '2023-10-27 10:35:12.123456'::timestamp); -> 2023-10-01 00:00:00
- 例: 2023年6月は第2四半期です。第2四半期の始まりは4月1日です。
- SELECT date_trunc('quarter', '2023-06-15 10:00:00'::timestamp); -> 2023-04-01 00:00:00
year: 年以下の部分を切り捨てます。これにより、その年の1月1日の真夜中0時0分0秒が得られます。
- 例: SELECT date_trunc('year', '2023-10-27 10:35:12.123456'::timestamp); -> 2023-01-01 00:00:00
decade: 10年単位で切り捨てます。例えば2020年代であれば2020-01-01 00:00:00になります。
- 例: SELECT date_trunc('decade', '2023-10-27 10:35:12.123456'::timestamp); -> 2020-01-01 00:00:00
- 例: SELECT date_trunc('decade', '1999-12-31 23:59:59'::timestamp); -> 1990-01-01 00:00:00
century: 100年単位で切り捨てます。例えば21世紀（2001年～2100年）であれば2001-01-01 00:00:00になります。PostgreSQLの世紀の定義は、西暦N年が「(N-1)/100 + 1」世紀となるようです。つまり1世紀は1年～100年、20世紀は1901年～2000年、21世紀は2001年～2100年となります。
- 例: SELECT date_trunc('century', '2023-10-27 10:35:12.123456'::timestamp); -> 2001-01-01 00:00:00
- 例: SELECT date_trunc('century', '2000-12-31 23:59:59'::timestamp); -> 1901-01-01 00:00:00
- 例: SELECT date_trunc('century', '1900-01-01 00:00:00'::timestamp); -> 1801-01-01 00:00:00
millennium: 1000年単位で切り捨てます。例えば3千年紀（2001年～3000年）であれば2001-01-01 00:00:00になります。世紀と同様に、「(N-1)/1000 + 1」千年紀となるようです。
- 例: SELECT date_trunc('millennium', '2023-10-27 10:35:12.123456'::timestamp); -> 2001-01-01 00:00:00
- 例: SELECT date_trunc('millennium', '2000-12-31 23:59:59'::timestamp); -> 1001-01-01 00:00:00

これらの field の値は、大文字小文字を区別しません（例: 'DAY' も 'day' と同じ）。

様々なデータ型での使用

date_trunc 関数は、TIMESTAMP WITH TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, DATE, TIME, INTERVAL といった複数の日付・時刻関連データ型に対して使用できます。それぞれの型に対する挙動を見ていきましょう。

`TIMESTAMP WITHOUT TIME ZONE` (`timestamp`)

最も一般的な使い方の1つです。タイムゾーン情報を持たない日時データに対して、指定した単位で切り捨てを行います。

“`sql
SELECT date_trunc(‘hour’, ‘2023-10-27 14:55:30.987’::timestamp);
— 結果: 2023-10-27 14:00:00

SELECT date_trunc(‘day’, ‘2023-10-27 14:55:30.987’::timestamp);
— 結果: 2023-10-27 00:00:00

SELECT date_trunc(‘month’, ‘2023-10-27 14:55:30.987’::timestamp);
— 結果: 2023-10-01 00:00:00

SELECT date_trunc(‘year’, ‘2023-10-27 14:55:30.987’::timestamp);
— 結果: 2023-01-01 00:00:00
“`

この場合、サーバーまたはセッションの現在のタイムゾーン設定は、切り捨ての計算そのものには直接影響しません。あくまで入力された文字列または値が持つ「そのままの日時」として解釈され、そのローカルタイムで切り捨てが行われます。ただし、week, quarter, year といったフィールドの場合、日付の境界線（例えば年の始まりが1月1日であること）は普遍的なものであるため、タイムゾーンによる差異は発生しにくいです。時間より細かい単位では、単に数値的な切り捨てが行われます。

オプションの time_zone 引数を指定した場合、source のタイムスタンプはまず指定されたタイムゾーンのオフセットを持つものとして扱われ、そのタイムゾーンで切り捨てが行われます。

“`sql
— セッションタイムゾーンが ‘UTC’ の場合を想定
SELECT date_trunc(‘day’, ‘2023-10-27 01:00:00’::timestamp, ‘UTC+9’);
— ‘2023-10-27 01:00:00’ は UTC+9 では 2023-10-26 16:00:00 UTC に相当
— UTC+9 で切り捨てると 2023-10-27 00:00:00 になる
— 結果は timestamp 型なのでタイムゾーン情報は含まれない
— 結果: 2023-10-27 00:00:00

— タイムゾーン引数なし (セッションタイムゾーンで処理)
SELECT date_trunc(‘day’, ‘2023-10-27 01:00:00’::timestamp);
— セッションタイムゾーンが ‘UTC+9’ なら 2023-10-27 00:00:00
— セッションタイムゾーンが ‘UTC’ なら 2023-10-27 00:00:00
— timestamp型の場合、タイムゾーン引数がないと内部でタイムゾーンを考慮せずにローカル時刻として扱われることが多い
— ただし、週や四半期などの計算では内部的にUTCに変換してから行う場合があるため、挙動には注意が必要。
— 一般的には timestamp 型には time_zone 引数を使わない方が混乱が少ない。
``timestamp without time zoneにtime_zone引数を指定するケースは稀であり、挙動が直感的でない場合があるため、通常はこの引数はtimestamptzに対して使用するか、あるいはtimestamp` 型には指定しない方が安全です。

`TIMESTAMP WITH TIME ZONE` (`timestamptz`)

タイムゾーン情報を持つ日時データに対して date_trunc を使用する場合、PostgreSQLはまずその値を内部的にUTC（協定世界時）に変換し、そのUTC時刻に対して切り捨て計算を行います。そして、結果をセッションのタイムゾーン（SET timezone = '...' で設定した値、デフォルトはサーバーの設定）に戻して返します。

“`sql
— セッションタイムゾーンを ‘UTC’ に設定
SET timezone = ‘UTC’;
SELECT date_trunc(‘hour’, ‘2023-10-27 14:55:30.987+09’::timestamptz);
— ‘2023-10-27 14:55:30.987+09’ は UTC では ‘2023-10-27 05:55:30.987’
— UTC で時間単位で切り捨て -> ‘2023-10-27 05:00:00’ UTC
— セッションタイムゾーン (UTC) に戻す -> ‘2023-10-27 05:00:00+00’
— 結果: 2023-10-27 05:00:00+00

— セッションタイムゾーンを ‘Asia/Tokyo’ (UTC+9) に設定
SET timezone = ‘Asia/Tokyo’;
SELECT date_trunc(‘hour’, ‘2023-10-27 14:55:30.987+09’::timestamptz);
— ‘2023-10-27 14:55:30.987+09’ は UTC では ‘2023-10-27 05:55:30.987’
— UTC で時間単位で切り捨て -> ‘2023-10-27 05:00:00’ UTC
— セッションタイムゾーン (Asia/Tokyo) に戻す -> ‘2023-10-27 14:00:00+09’
— 結果: 2023-10-27 14:00:00+09
`` このように、timestamptzに対してdate_truncを使う場合、結果はセッションのタイムゾーンに依存します。これは集計などで異なるタイムゾーンのデータを扱う際に重要になります。特定のタイムゾーンで切り捨てを行いたい場合は、date_trunc` の第3引数にタイムゾーンを指定します。この場合、そのタイムゾーンでのローカル時刻として解釈し、切り捨て計算が行われます。

sql -- セッションタイムゾーンに関わらず 'America/New_York' (EST: UTC-5, EDT: UTC-4) で切り捨て SELECT date_trunc('day', '2023-10-27 14:55:30.987+09'::timestamptz, 'America/New_York'); -- '2023-10-27 14:55:30.987+09' は 'America/New_York' では 2023-10-27 01:55:30.987 -- America/New_York で日単位で切り捨て -> 2023-10-27 00:00:00 America/New_York -- 結果はセッションタイムゾーンに変換されて表示される (例えば Asia/Tokyo なら +09 がつく) -- 結果: 2023-10-27 13:00:00+09 (Asia/Tokyo で表示した場合)
timestamptz に対して time_zone 引数を指定した場合、PostgreSQLは入力の timestamptz を一度UTCに変換し、次にそのUTC時刻を time_zone 引数で指定されたタイムゾーンのローカル時刻に変換してから、そのローカル時刻に対して切り捨てを行い、最後に結果をセッションのタイムゾーンに戻して表示します。これは少し複雑ですが、特定のタイムゾーンでのレポートを作成する際などに役立ちます。

`DATE`

DATE 型は日付のみを保持し、時刻情報は持ちません。date_trunc 関数は、DATE 型を入力として受け取ることができますが、結果は TIMESTAMP 型になります。これは、date_trunc が常に時刻部分を持つ TIMESTAMP 型を生成するためです。field が 'day' より大きい単位（'week', 'month', 'year' など）の場合、結果の日付は適切に切り捨てられますが、時刻部分は常に 00:00:00 になります。field が 'day' より小さい単位（'hour', 'minute', 'second' など）の場合、入力は日付しか持たないため、結果はその日付の 00:00:00 となり、指定した細かい単位での切り捨ては実質的に意味を持ちません。

“`sql
SELECT date_trunc(‘day’, ‘2023-10-27’::date);
— 結果: 2023-10-27 00:00:00 (TIMESTAMP 型)

SELECT date_trunc(‘month’, ‘2023-10-27’::date);
— 結果: 2023-10-01 00:00:00 (TIMESTAMP 型)

SELECT date_trunc(‘year’, ‘2023-10-27’::date);
— 結果: 2023-01-01 00:00:00 (TIMESTAMP 型)

SELECT date_trunc(‘hour’, ‘2023-10-27’::date);
— 結果: 2023-10-27 00:00:00 (TIMESTAMP 型) – 時刻情報がないため時間単位の切り捨ては0時になる
``DATE型を入力として使用する場合、結果がTIMESTAMP型になることに注意が必要です。もし結果としてDATE型が欲しい場合は、さらに::date` でキャストする必要があります。

sql SELECT date_trunc('month', '2023-10-27'::date)::date; -- 結果: 2023-10-01 (DATE 型)

`TIME`

TIME 型は時刻のみを保持し、日付情報は持ちません。date_trunc 関数は TIME 型も受け付けますが、これは主に秒以下の単位（'second', 'millisecond', 'microsecond') での時間切り捨てにのみ有効です。これより大きい単位（'minute', 'hour'）を指定した場合、結果はその時刻の 00:00:00 となり、あまり実用的ではありません。結果は TIME 型として返されます。

“`sql
SELECT date_trunc(‘second’, ’14:55:30.987′::time);
— 結果: 14:55:30 (TIME 型)

SELECT date_trunc(‘minute’, ’14:55:30.987′::time);
— 結果: 14:55:00 (TIME 型)

SELECT date_trunc(‘hour’, ’14:55:30.987′::time);
— 結果: 14:00:00 (TIME 型)

— ‘day’ 以上の単位は TIME 型には無効、エラーまたは非直感的な結果になる可能性がある
— SELECT date_trunc(‘day’, ’14:55:30′::time); — エラーまたは NULL
``TIME型に対するdate_trunc` は、秒以下の精度を丸める場合にのみ限定的に利用されると考えられます。

`INTERVAL`

INTERVAL 型は期間（例えば ‘1 hour’, ‘2 days’, ‘1 month’）を表現します。date_trunc 関数は INTERVAL 型に対しても適用できます。これは、期間を指定した単位で「切り捨て」、つまり特定の単位より細かい部分をゼロにするために使用できます。

“`sql
SELECT date_trunc(‘hour’, ‘2 days 3 hours 45 minutes 12 seconds’::interval);
— 結果: 2 days 03:00:00 (INTERVAL 型)

SELECT date_trunc(‘day’, ‘2 days 3 hours 45 minutes 12 seconds’::interval);
— 結果: 2 days 00:00:00 (INTERVAL 型)

SELECT date_trunc(‘month’, ‘1 year 5 months 20 days’::interval);
— 結果: 1 year 5 mons 0 days (INTERVAL 型)

SELECT date_trunc(‘year’, ‘1 year 5 months 20 days’::interval);
— 結果: 1 year 0 mons 0 days (INTERVAL 型)
``INTERVAL型に対するdate_trunc` は、期間の構成要素を特定の単位で丸める（切り捨てる）際に便利です。例えば、正確な秒数ではなく「何日と何時間」だけを知りたい場合などに利用できます。

`date_trunc` の内部的な仕組み

date_trunc 関数がどのように機能するかを理解することは、特にタイムゾーンを扱う際に重要です。PostgreSQLの内部では、多くの日付・時刻操作は、基準となる時点（Unixエポックである1970年1月1日 00:00:00 UTC）からの経過時間（マイクロ秒または秒）として扱われます。

date_trunc(field, source) の計算は、概念的には以下のステップで行われます（特にタイムスタンプの場合）。

入力の正規化: source の日時データを内部的な表現（例えば、Unixエポックからの経過時間）に変換します。timestamptz の場合、これは通常UTCでの経過時間になります。timestamp の場合は、システムまたはセッションのタイムゾーンが考慮されるか、または単にローカル時間としての経過時間として扱われます（time_zone 引数が指定されていればそれが優先されます）。
切り捨て基準点の計算: 指定された field に基づいて、切り捨ての基準となる時刻を計算します。例えば、'day' で切り捨てる場合、基準点はその日の始まり（00:00:00）になります。この基準点は、入力の日時と同じ日、同じ月、同じ年などに含まれる、指定単位の最も早い時刻です。この計算は通常、UTCまたは指定されたタイムゾーンで行われます。
基準点への調整: 入力の日時を、計算された基準点まで「巻き戻し」ます。例えば、'hour' で切り捨てる場合、分の部分と秒以下の部分をゼロにします。
結果の変換: 調整された日時を、元のデータ型（または DATE の場合は TIMESTAMP）に戻します。timestamptz の場合、最終的な表示はセッションのタイムゾーンに変換されます。

week フィールドの場合、ISO 8601週の始まりは常に月曜日です。date_trunc('week', ...) は、入力された日時が含まれるISO週の月曜日の真夜中（00:00:00）を返します。これは、単に7日前の日付を計算するのではなく、特定のカレンダーシステムに基づいた計算です。

quarter フィールドの場合、四半期は1月1日、4月1日、7月1日、10月1日から始まります。date_trunc('quarter', ...) は、入力された日時が含まれる四半期の最初の月の1日の真夜中を返します。

このように、date_trunc は単なる数値的な切り捨てではなく、日付・時刻のカレンダー構造を考慮した切り捨てを行います。特に timestamptz の場合は、タイムゾーン変換が伴うため、結果がどのタイムゾーンの時間を示すのかを常に意識する必要があります。デフォルトではセッションタイムゾーンですが、time_zone 引数を使えば特定のタイムゾーンで切り捨てを行うことも可能です。

応用例

date_trunc 関数は、実際のデータ分析やアプリケーション開発で非常に役立ちます。いくつかの代表的な応用例を見てみましょう。

1. 日、週、月、年ごとの集計

最も一般的な用途の一つです。例えば、売上テーブル (sales) に sale_time (timestamptz) と amount (numeric) カラムがあるとします。日ごとの売上合計を知りたい場合、sale_time を日単位で切り捨ててから GROUP BY 句で使用します。

“`sql
— 日ごとの売上合計
SELECT
date_trunc(‘day’, sale_time) AS sale_day,
SUM(amount) AS daily_sales
FROM
sales
GROUP BY
sale_day
ORDER BY
sale_day;

— 週ごとの売上合計
SELECT
date_trunc(‘week’, sale_time) AS sale_week,
SUM(amount) AS weekly_sales
FROM
sales
GROUP BY
sale_week
ORDER BY
sale_week;

— 月ごとの売上合計
SELECT
date_trunc(‘month’, sale_time) AS sale_month,
SUM(amount) AS monthly_sales
FROM
sales
GROUP BY
sale_month
ORDER BY
sale_month;

— 四半期ごとの売上合計
SELECT
date_trunc(‘quarter’, sale_time) AS sale_quarter,
SUM(amount) AS quarterly_sales
FROM
sales
GROUP BY
sale_quarter
ORDER BY
sale_quarter;

— 年ごとの売上合計
SELECT
date_trunc(‘year’, sale_time) AS sale_year,
SUM(amount) AS yearly_sales
FROM
sales
GROUP BY
sale_year
ORDER BY
sale_year;
“`
これらのクエリは、指定した期間の始まり（例: 日の始まり、週の月曜日、月の1日）をキーとして集計を行います。これは、期間ごとのトレンド分析やレポート作成に不可欠です。

2. 特定期間のデータ抽出

date_trunc を WHERE 句と組み合わせて、特定の期間内のデータを抽出することができます。例えば、「今月」の売上データだけを取得したい場合、sale_time を月単位で切り捨てた結果が現在の月の始まりと一致するかどうかを条件にします。

“`sql
— 今月の売上データを抽出 (セッションタイムゾーン基準)
SELECT *
FROM sales
WHERE date_trunc(‘month’, sale_time) = date_trunc(‘month’, NOW());

— 特定の月の売上データを抽出 (例: 2023年10月)
SELECT *
FROM sales
WHERE date_trunc(‘month’, sale_time) = ‘2023-10-01’::date::timestamp;
— または、より安全に timestamptz リテラルを使う
WHERE date_trunc(‘month’, sale_time) = ‘2023-10-01 00:00:00+00’::timestamptz; — 必要に応じてタイムゾーン調整

— 今日の売上データを抽出
SELECT *
FROM sales
WHERE date_trunc(‘day’, sale_time) = date_trunc(‘day’, NOW());

— 今週の売上データを抽出
SELECT *
FROM sales
WHERE date_trunc(‘week’, sale_time) = date_trunc(‘week’, NOW());
``NOW()は現在のtimestamptzを返します。これをdate_trunc` で切り捨てることで、「今月の始まり」「今日の始まり」「今週の始まり」といった基準点を動的に取得できます。

あるいは、期間を指定して抽出することもできます。

sql -- 2023年10月1日から2023年10月31日までのデータ SELECT * FROM sales WHERE sale_time >= date_trunc('month', '2023-10-15'::date::timestamp) -- 月の始まりを取得 AND sale_time < date_trunc('month', '2023-10-15'::date::timestamp) + INTERVAL '1 month'; -- 翌月の始まりを取得 (これにより月末までを含む)
この方法 (>= start_date AND < end_date) は、期間の境界条件を正確に扱うためのベストプラクティスです。

3. 時系列データの正規化

時系列データ（例えばセンサーの測定値やログデータ）は、不均一な間隔で記録されることがあります。分析のために、データを一定の間隔（例えば1時間ごと、1日ごと）に正規化したい場合があります。date_trunc を使ってタイムスタンプを丸めることで、これを実現できます。

sql -- センサーデータ (timestamp, value) を1時間ごとに集計 SELECT date_trunc('hour', measurement_time) AS measurement_hour, AVG(value) AS hourly_average_value FROM sensor_data GROUP BY measurement_hour ORDER BY measurement_hour;
これにより、各時間ブロック内のデータの平均値などを計算し、均一な時間間隔での時系列データを生成できます。

4. データの比較や結合のキーとして利用

異なるテーブルにある日時データを比較したり結合したりする場合、精度の違いが問題になることがあります。date_trunc を使って両方のテーブルの日時データを同じ粒度（例えば日単位）に切り捨ててから比較・結合することで、この問題を解決できます。

sql -- 注文テーブルと配送テーブルを日単位で結合 SELECT o.order_day, COUNT(o.order_id) AS daily_orders, COUNT(d.delivery_id) AS daily_deliveries FROM (SELECT order_id, date_trunc('day', order_time) AS order_day FROM orders) o LEFT JOIN (SELECT delivery_id, date_trunc('day', delivery_time) AS delivery_day FROM deliveries) d ON o.order_day = d.delivery_day GROUP BY o.order_day ORDER BY o.order_day;
これにより、特定の日における注文数と配送数を比較するといった分析が可能になります。

5. 期間判定（特定の週、月、四半期に含まれるか）

データが特定の期間（週、月、四半期、年など）に含まれるかを判定する際に、date_trunc を利用できます。

sql -- 特定の日付 ('2023-10-27') が含まれる週のデータを抽出 SELECT * FROM sales WHERE date_trunc('week', sale_time) = date_trunc('week', '2023-10-27'::date);
このクエリは、「sale_time を週単位で切り捨てた値」と、「'2023-10-27' を週単位で切り捨てた値（つまり、2023年10月27日が含まれる週の月曜日の日付）」が等しいレコードを抽出します。これにより、特定の日付を含む週全体のデータを簡単に取得できます。

`date_trunc` vs その他の日付・時刻関数

PostgreSQLには date_trunc 以外にも多くの日付・時刻関連関数があります。date_trunc と混同しやすい関数や、使い分けを理解しておきたい関数について説明します。

`EXTRACT()` / `DATE_PART()`

EXTRACT(field FROM source) または DATE_PART('field', source) は、日時データから特定の要素（年、月、日、時、分、秒、曜日、週番号など）を数値として取り出す関数です。

sql SELECT EXTRACT(year FROM '2023-10-27 10:35:12'::timestamp); -- 結果: 2023 (numeric) SELECT EXTRACT(month FROM '2023-10-27 10:35:12'::timestamp); -- 結果: 10 (numeric) SELECT EXTRACT(day FROM '2023-10-27 10:35:12'::timestamp); -- 結果: 27 (numeric) SELECT EXTRACT(hour FROM '2023-10-27 10:35:12'::timestamp); -- 結果: 10 (numeric) SELECT EXTRACT(dow FROM '2023-10-27 10:35:12'::timestamp); -- 結果: 5 (numeric, 0=日曜...6=土曜) SELECT EXTRACT(week FROM '2023-10-27 10:35:12'::timestamp); -- 結果: 43 (numeric, ISO 8601 週番号)
EXTRACT / DATE_PART は「特定の部分の値を取り出す」のに対し、date_trunc は「指定した粒度で切り捨てた新しい日時データを生成する」という点で大きく異なります。

EXTRACT(month FROM some_timestamp) -> 10 (数値)
date_trunc('month', some_timestamp) -> 2023-10-01 00:00:00 (タイムスタンプ)

集計のキーとして使用する場合、EXTRACT を使うと年や月といった数値になりますが、date_trunc を使うと期間の開始を示すタイムスタンプになります。通常、期間集計では date_trunc の方が直感的で、期間同士の比較や結合も容易になります。

`TRUNC()` / `FLOOR()`

TRUNC() および FLOOR() は、数値に対する切り捨て関数です。これらは日付・時刻データには直接適用できません。

sql SELECT TRUNC(123.456, 2); -- 結果: 123.45 (numeric) SELECT FLOOR(123.456); -- 結果: 123 (numeric)
名前が似ていますが、date_trunc は日時専用の関数であり、これらの数値関数とは全く異なります。

`ROUND()`

ROUND() 関数は数値を丸める関数です。日付・時刻データに対しては、ROUND() に似た挙動をする関数はありませんが、date_trunc を応用して「丸め」をエミュレートすることは可能です。例えば、最も近い時間（時）に丸めたい場合：

sql -- '2023-10-27 10:35:12' を最も近い時間 (10時または11時) に丸める SELECT date_trunc('hour', '2023-10-27 10:35:12'::timestamp + INTERVAL '30 minutes'); -- 10:35:12 に 30分 (時間の半分) を加えて 11:05:12 にしてから時間で切り捨て -> 11:00:00 SELECT date_trunc('hour', '2023-10-27 10:25:12'::timestamp + INTERVAL '30 minutes'); -- 10:25:12 に 30分を加えて 10:55:12 にしてから時間で切り捨て -> 10:00:00
このように、指定した単位の半分の期間を加えてから date_trunc で切り捨てることで、その単位での「四捨五入」に似た丸め処理を実現できます。

注意点とトラブルシューティング

date_trunc 関数を使用する際に注意すべき点や、問題が発生した場合の確認ポイントを挙げます。

タイムゾーンの重要性

timestamptz 型を扱う場合、date_trunc の結果はセッションのタイムゾーンに依存します。異なるタイムゾーン設定のユーザーやアプリケーションから同じクエリを実行すると、異なる結果が表示される可能性があります。常にどのタイムゾーンで計算が行われ、どのタイムゾーンで結果が表示されるのかを意識することが重要です。

計算: timestamptz は内部的にUTCで計算され、field による切り捨てはUTCに対して行われます（time_zone 引数がない場合）。time_zone 引数がある場合は、指定されたタイムゾーンのローカル時刻として計算されます。
表示: timestamptz の結果は、常にセッションのタイムゾーンに変換されて表示されます。

集計などを行う際は、セッションのタイムゾーンを明示的に設定するか (SET timezone = '...')、または date_trunc の第3引数でタイムゾーンを指定する (date_trunc(field, source, time_zone)) ことで、結果の一貫性を確保できます。

`week` の挙動

date_trunc('week', ...) はISO 8601標準に従い、週の始まりを月曜日とします。もし日曜日始まりの週（北米などで一般的）で集計したい場合は、date_trunc をそのまま使うのではなく、他の方法を検討する必要があります。例えば、日付から曜日を計算し、週の開始日を手動で調整するなどの方法が考えられます。

例：日曜日始まりの週の開始日を取得

“`sql
— ‘2023-10-27’ は金曜日
— ISO週の開始日: 2023-10-23 (月)
SELECT date_trunc(‘week’, ‘2023-10-27’::date);

— 日曜日始まりの週の開始日 (金曜日から4日前の日曜日)
SELECT ‘2023-10-27’::date – EXTRACT(dow FROM ‘2023-10-27’::date)::int * INTERVAL ‘1 day’;
— EXTRACT(dow…) は日曜日を0、月曜日を1…土曜日を6と返す
— 2023-10-27 は金曜日なので EXTRACT(dow…) は 5 を返す
— ‘2023-10-27’ – 5 * ‘1 day’ = ‘2023-10-27’ – ‘5 days’ = ‘2023-10-22’ (日)
`` このように、EXTRACT(dow …)` を利用して日曜日始まりの週の開始日を計算できます。

`quarter` の挙動

date_trunc('quarter', ...) は、PostgreSQLのデフォルト設定に基づいた四半期の開始日（1月1日, 4月1日, 7月1日, 10月1日）を使用します。もし会計年度やその他のカスタム四半期を使用している場合は、これも手動で計算する必要があります。

パフォーマンスへの影響

大規模なテーブルで date_trunc を WHERE 句や GROUP BY 句で使用する場合、パフォーマンスに影響を与える可能性があります。関数を適用したカラムに対しては通常のインデックスは利用されません。

例えば、以下のクエリは sale_time のインデックスを利用できません。

sql SELECT * FROM sales WHERE date_trunc('day', sale_time) = '2023-10-27'::date;

このような場合、関数インデックスを作成することでパフォーマンスを改善できる場合があります。

“`sql
— 日単位の切り捨てに対する関数インデックスを作成
CREATE INDEX sales_sale_time_day_idx ON sales (date_trunc(‘day’, sale_time));

— 月単位の切り捨てに対する関数インデックスを作成
CREATE INDEX sales_sale_time_month_idx ON sales (date_trunc(‘month’, sale_time));
`` 関数インデックスを作成することで、date_trunc` を使用した絞り込みや集計が高速化されます。ただし、関数インデックスは作成・維持にコストがかかるため、必要な場合にのみ使用を検討してください。

また、期間抽出においては、date_trunc を使わずに、元のカラムと計算された期間の境界値で比較する方がインデックスを利用しやすく、パフォーマンスが良いことが多いです。

sql -- パフォーマンスの良い期間抽出方法 SELECT * FROM sales WHERE sale_time >= date_trunc('month', NOW()) AND sale_time < date_trunc('month', NOW()) + INTERVAL '1 month';
このように、抽出条件では date_trunc を「比較対象の生成」に使い、元のカラムを比較の左辺に置くことで、インデックスの利用を促進できます。

NULL値の扱い

date_trunc 関数の source 引数がNULLの場合、結果もNULLになります。field 引数がNULLの場合はエラーになります。

sql SELECT date_trunc('day', NULL); -- 結果: NULL SELECT date_trunc(NULL, NOW()); -- エラー

まとめ

PostgreSQLの date_trunc 関数は、日時データを指定した時間単位（年、月、日、時、分、秒など）で切り捨てるための非常に強力で便利な関数です。これにより、データの粒度を統一し、集計や分析を容易に行うことができます。

基本構文は date_trunc(field, source [, time_zone]) です。
field には切り捨てたい単位を指定します ('year', 'month', 'day', 'hour', 'week' など多数)。
source には日時データ（timestamp, timestamptz, date, time, interval）を指定します。
timestamptz を扱う際はタイムゾーンの挙動に注意が必要です。第3引数で明示的にタイムゾーンを指定することも可能です。
集計（GROUP BY）、期間抽出（WHERE）、時系列データの正規化など、幅広い応用例があります。
EXTRACT/DATE_PART は要素を取り出す関数であり、date_trunc とは目的が異なります。
大規模データに対する date_trunc の利用はパフォーマンスに影響を与える可能性があり、関数インデックスの利用や WHERE 句の記述方法で最適化を検討できます。

date_trunc を使いこなすことで、PostgreSQLデータベース上の時間に関するデータをより効率的に、より正確に扱うことができるようになります。このガイドが、あなたのPostgreSQLデータ操作の一助となれば幸いです。

付録：参考情報

PostgreSQL 公式ドキュメント: 日付/時刻関数と演算子 – date_trunc
https://www.postgresql.jp/document/current/html/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC (日本語)
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC (英語)

（文字数カウントの結果によっては、さらに詳細な説明や例を追加して5000語に近づける調整が必要です。上記は構成に基づいた叩き台であり、最終的な単語数は調整が必要です。）

【自己評価と調整案】
現状の記述で各セクションを詳細に説明すると、5000語はかなり達成できる可能性が高い。特に応用例で、もう少し具体的なテーブル構造やビジネスシナリオを想定したクエリ例を増やす。内部仕組みやタイムゾーンの挙動について、より丁寧に解説する。注意点では、それぞれのケースについてより多くの具体例や回避策を提示する。他の関数との比較も、それぞれのユースケースをもっと明確に描き出す。

例えば、応用例のセクションで、各集計レベル（日、週、月など）ごとに、より詳細な集計（例：日ごとの売上トップ10商品、月ごとの新規顧客数）を求めるクエリ例を追加する。また、時系列分析の例として、移動平均の計算などで date_trunc をどう使うかのヒントを示す。

注意点では、特定のタイムゾーン（夏時間を含む場所など）での date_trunc の挙動について、より複雑な例を挙げて説明する。パフォーマンスのセクションで、EXPLAIN を使った実行計画の確認方法に触れる。

これらの詳細化を行うことで、単語数を確保しつつ、ユーザーにとってより実践的で役立つ内容になるはず。

ただし、今回は「記事の内容を直接表示」が指示なので、上記の調整案を基に、そのまま記述を続行します。

（ここから、上記調整案を盛り込みながら、より詳細な記述を追加していきます。）

さらに詳細な応用例

前述の基本応用例に加えて、より複雑なシナリオでの date_trunc の活用方法を探ります。

6. 特定の時間帯での集計

1日の中で特定の時間帯（例えば朝9時から夕方5時まで）に発生したイベントだけを集計したい場合があります。date_trunc('day', ...) で日付部分を固定し、時間部分でフィルタリングすることで実現できます。

sql -- 各日について、9時から17時までの売上合計 SELECT date_trunc('day', sale_time) AS sale_day, SUM(amount) AS daytime_sales FROM sales WHERE -- sale_time の時間部分が 9:00 から 17:00 の間にあるかを判定 EXTRACT(hour FROM sale_time) >= 9 AND EXTRACT(hour FROM sale_time) < 17 GROUP BY sale_day ORDER BY sale_day;
ここでは EXTRACT を使って時間部分を取り出していますが、これは date_trunc そのものというよりは、日付/時刻操作全般における連携の例です。

7. コホート分析における期間の定義

コホート分析では、「同じ期間（例: 同じ月に登録したユーザー）に属するグループ」の行動を追跡します。この「同じ期間」を定義する際に date_trunc が非常に役立ちます。

例：ユーザー登録月ごとの、登録から3ヶ月以内の購入回数を集計

“`sql
— ユーザー登録テーブル users (user_id, registration_time)
— 購入テーブル purchases (purchase_id, user_id, purchase_time)

SELECT
reg_cohort.registration_month,
COUNT(p.purchase_id) AS purchases_in_first_3_months
FROM
( — 登録月コホートを定義
SELECT
user_id,
date_trunc(‘month’, registration_time) AS registration_month
FROM users
) reg_cohort
JOIN
purchases p ON reg_cohort.user_id = p.user_id
WHERE
— 購入時間が登録月の開始日から3ヶ月以内であるかを判定
p.purchase_time >= reg_cohort.registration_month
AND p.purchase_time < reg_cohort.registration_month + INTERVAL ‘3 months’
GROUP BY
reg_cohort.registration_month
ORDER BY
reg_cohort.registration_month;
`` このように、date_trunc(‘month’, …)` でユーザーの「登録月」というコホートキーを作成し、そのキーと購入時間を使って期間内の行動をフィルタリングしています。

8. 日付/時刻データのグルーピングと順序付け

ログデータのタイムスタンプを date_trunc で丸めてからグループ化し、時間順に並べることで、特定の期間（例えば分ごと、時間ごと）のログエントリを確認しやすくなります。

“`sql
— ログテーブル logs (log_time, message)

— 1分ごとのログ数を集計し、時間順に表示
SELECT
date_trunc(‘minute’, log_time) AS log_minute,
COUNT(*) AS log_count
FROM
logs
GROUP BY
log_minute
ORDER BY
log_minute;
“`

9. タイムスタンプの特定の要素だけを保持したい場合（応用編）

date_trunc は常に指定した単位より細かい部分を「ゼロ」にしますが、時には特定の単位だけを保持し、残りをゼロにしたい場合があります。例えば、「日と時間」だけを保持し、分以下をゼロにしたい場合です。これは date_trunc('hour', ...) で実現できます。

しかし、さらに進んで、「年の情報だけが必要で、月日以下は全て1月1日 00:00:00 にしたい」という場合は date_trunc('year', ...) です。

「各レコードのタイムスタンプが、その日の何時何分何秒か」だけを取り出し、日付部分を無視したい場合は、date_trunc は直接使えません。これは時刻部分を取り出す別の関数（例：EXTRACT(hour from ...), EXTRACT(minute from ...)）や、型キャスト (::time) を使うことになります。

sql -- タイムスタンプから時間部分のみを TIME 型で取得 SELECT '2023-10-27 10:35:12'::timestamp::time; -- 結果: 10:35:12
このように、目的によって適切な関数を選択することが重要です。date_trunc はあくまで「指定した単位で切り捨てた日時データ」を生成します。

`date_trunc` 関数のより深い理解：タイムゾーンと内部処理

timestamptz と date_trunc の挙動は、タイムゾーンの仕組みを理解する上で特に重要です。PostgreSQLは timestamptz 値を内部的にUTCで格納します。表示する際に、セッションのタイムゾーン設定に基づいてUTCから変換して表示します。

date_trunc('day', source::timestamptz) の計算は、以下のようになります。

source::timestamptz が表現するUTC時刻を取得します。
そのUTC時刻を、計算を行うタイムゾーン（デフォルトはセッションタイムゾーン、第3引数があればそのタイムゾーン）でのローカル時刻に変換します。
そのローカル時刻に対して、指定された field (ここでは 'day') で切り捨てを行います（つまり、そのローカル日付の真夜中0時0分0秒に設定します）。
切り捨てられたローカル時刻を再びUTCに変換します。
その結果のUTC時刻を、最終的にセッションのタイムゾーンに変換して表示します。

例で確認:
セッションタイムゾーン: 'Asia/Tokyo' (UTC+9)
入力: '2023-10-27 01:00:00+09'::timestamptz

入力UTC: '2023-10-27 01:00:00+09' は UTC では '2023-10-26 16:00:00' UTC です。
計算タイムゾーンのローカル時刻: 計算タイムゾーンがセッションタイムゾーン 'Asia/Tokyo' の場合、UTC '2023-10-26 16:00:00' は 'Asia/Tokyo' では '2023-10-27 01:00:00' です。
切り捨て: このローカル時刻 '2023-10-27 01:00:00' を 'day' 単位で切り捨てると、'2023-10-27 00:00:00' になります。
UTCに戻す: ローカル時刻 '2023-10-27 00:00:00' (‘Asia/Tokyo’) は、UTC では '2023-10-26 15:00:00' UTC です。
表示: 結果のUTC '2023-10-26 15:00:00' をセッションタイムゾーン 'Asia/Tokyo' に変換して表示します。これは '2023-10-27 00:00:00+09' と表示されます。

計算タイムゾーンを明示的に指定した場合:
セッションタイムゾーン: 'Asia/Tokyo' (UTC+9)
入力: '2023-10-27 01:00:00+09'::timestamptz
date_trunc('day', '2023-10-27 01:00:00+09'::timestamptz, 'America/New_York')

入力UTC: '2023-10-27 01:00:00+09' は UTC では '2023-10-26 16:00:00' UTC です。
計算タイムゾーンのローカル時刻: 計算タイムゾーンが 'America/New_York' (EST: UTC-5) の場合、UTC '2023-10-26 16:00:00' は 'America/New_York' では '2023-10-26 11:00:00' です。(時期によってEDT: UTC-4になる可能性もありますが、例としてESTで考えます)
切り捨て: このローカル時刻 '2023-10-26 11:00:00' を 'day' 単位で切り捨てると、'2023-10-26 00:00:00' になります。
UTCに戻す: ローカル時刻 '2023-10-26 00:00:00' (‘America/New_York’ EST) は、UTC では '2023-10-26 05:00:00' UTC です。
表示: 結果のUTC '2023-10-26 05:00:00' をセッションタイムゾーン 'Asia/Tokyo' に変換して表示します。これは '2023-10-26 14:00:00+09' と表示されます。

このように、timestamptz に対して date_trunc を行う場合、計算が行われるタイムゾーンが非常に重要であることがわかります。デフォルトはセッションタイムゾーンですが、第3引数で変更できます。

timestamp without time zone に time_zone 引数を指定した場合の挙動も同様です。入力 timestamp はまず指定されたタイムゾーンのローカル時刻として扱われ、その時刻で切り捨て計算が行われ、結果はタイムゾーン情報を持たない timestamp として返されます。

sql SELECT date_trunc('day', '2023-10-27 01:00:00'::timestamp, 'UTC+9'); -- '2023-10-27 01:00:00' を UTC+9 のローカル時刻として扱う -- そのローカル時刻を日単位で切り捨て -> '2023-10-27 00:00:00' UTC+9 のローカル時刻 -- 結果は timestamp なので '2023-10-27 00:00:00'
この場合、入力タイムスタンプのタイムゾーンは「指定されたタイムゾーン」と仮定され、その仮定の下で切り捨て計算が行われるイメージです。結果はタイムゾーンを持たない timestamp になります。

date_trunc 関数のフィールドと対応する期間の長さ

各 field がどのくらいの期間に対応するかを理解しておくと、より正確な集計や分析ができます。ただし、week, month, quarter, year などの期間は固定長ではありません（週は常に7日ですが、月は28～31日、年は365日または366日）。

microsecond: 1 マイクロ秒
millisecond: 1 ミリ秒
second: 1 秒
minute: 1 分
hour: 1 時間
day: 1 日
week: 7 日 (固定)
month: 28～31 日 (可変)
quarter: 約 90～92 日 (可変)
year: 365 または 366 日 (可変)
decade: 約 3650～3653 日 (可変)
century: 約 36500～36525 日 (可変)
millennium: 約 365000～365250 日 (可変)

集計期間を定義する際は、date_trunc で期間の開始点を取得し、INTERVAL を使って期間の終了点を計算する方が、期間の不定長性を考慮できて安全です。（例: ... WHERE sale_time >= date_trunc('month', ...) AND sale_time < date_trunc('month', ...) + INTERVAL '1 month')

まとめ

PostgreSQLの date_trunc 関数は、日付や時刻データを特定の時間単位で切り捨てるための、データベース操作における強力なツールです。生のタイムスタンプデータを分析可能な粒度に変換することで、集計、レポート作成、時系列分析など、多岐にわたるタスクを効率的に実行できるようになります。

この関数を効果的に使用するためには、各 field の意味、異なるデータ型（特に timestamp と timestamptz）に対する挙動、タイムゾーンの処理、そして他の関連関数との違いを正確に理解することが不可欠です。特に timestamptz を扱う際のタイムゾーンの影響は、意図しない結果を避けるために細心の注意が必要です。

date_trunc 関数は、単なるテクニカルな機能に留まらず、ビジネス上の意思決定を支援するためのデータ集計や分析の基盤を提供します。本ガイドを通じて、date_trunc 関数の使い方とその奥深さをご理解いただけたことと思います。ぜひ、あなたのPostgreSQLデータベース操作に積極的に活用してみてください。

より高度な日付/時刻操作や、特定のカレンダー（会計年度など）に基づく集計が必要な場合は、date_trunc の結果をさらに加工したり、他の関数と組み合わせたり、またはカスタム関数を作成したりすることを検討してください。PostgreSQLの柔軟な日付/時刻システムは、複雑な要件にも対応できる能力を持っています。

最後に、データベースのバージョンによって date_trunc の挙動に微妙な違いがある可能性もゼロではありません。不明な点がある場合は、常に使用しているPostgreSQLのバージョンの公式ドキュメントを参照することをお勧めします。

これで、あなたは date_trunc 関数を「今すぐわかる！」レベルから、さらに一歩進んだ理解を持つことができたはずです。PostgreSQLでのデータ分析・活用において、この知識が大いに役立つことを願っています。