日付とタイムスタンプの関数

日付とタイムスタンプは、注文、ログイン、イベント、請求などあらゆる場所に存在します。で このレッスンでは、日付 (年、月など) から 部分を抽出する方法を見ていきます。 それを粒度 (月、週など) に切り捨てし、間隔を計算します と差異を確認し、期間をまたいで比較します。

基本タイプ

PostgreSQL は次の点を区別します。

• DATE — 日付のみ、形式は YYYY-MM-DD;
• TIMESTAMP — 日付 + 時刻、タイムゾーンなし。
• TIMESTAMPTZ — 日付 + 時刻 + タイムゾーン;
• INTERVAL — ピリオド (例: '3 days'、'2 hours 30 minutes')。

ecommerce データセットでは次のようになります。

EXTRACT — 部分を読む

EXTRACT(part FROM date) は数値を抽出します。

SELECT EXTRACT(YEAR  FROM ordered_at) AS year,
       EXTRACT(MONTH FROM ordered_at) AS month,
       EXTRACT(DOW   FROM ordered_at) AS day_of_week  -- 0=domenica
FROM   orders;

最も使用されるパーツ: YEAR、MONTH、DAY、HOUR、MINUTE、DOW (日) 第 0 週から第 6 週まで)、DOY (年間の日)。

DATE_TRUNC — 切り捨て

DATE_TRUNC('part', date) 日付を細かい単位で「切り捨て」ます。それは 月、週、時間ごとに集計する場合に必須:

-- Tutti gli ordini fatti in "marzo 2025" cadono nello stesso bucket:
SELECT DATE_TRUNC('month', ordered_at) AS month,
       COUNT(*) AS order_count
FROM   orders
GROUP BY month
ORDER BY month;

有効な部分: 'year'、'quarter'、'month'、'week'、'day'、 'hour'、'minute'、'second'。

読みやすさを考慮して、時間が必要ない場合は DATE にキャストすることがよくあります。 DATE_TRUNC('month', ordered_at)::date。

違いと間隔

2 つの TIMESTAMP を減算すると、INTERVAL が生成されます。

SELECT id,
       shipped_at - ordered_at AS wait_time
FROM   orders
WHERE  shipped_at IS NOT NULL;

INTERVAL リテラルとの比較:

WHERE  shipped_at - ordered_at > INTERVAL '1 day'

日付に日数を追加する:

SELECT signed_up_on + INTERVAL '30 days' AS trial_expiration
FROM   customers;

試してみてください

復習演習