0Pricing
SQL Academy · レッスン

EXTRACT、DATE_TRUNC、AGE

EXTRACTで日付の一部を取り出し、DATE_TRUNCでタイムスタンプを境界に切りそろえ、AGEで人間に読みやすい差分を計算します。

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

EXTRACT：日付の一部を取り出す

EXTRACT(part FROM ts) は、日付や時刻の単一の数値要素を返します。

SELECT EXTRACT(YEAR  FROM created_at) AS yr,
       EXTRACT(MONTH FROM created_at) AS mo,
       EXTRACT(DAY   FROM created_at) AS d,
       EXTRACT(DOW   FROM created_at) AS day_of_week
FROM orders;

よく使うEXTRACTの要素

便利な要素には、YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (0=Sun), DOY (1–366), WEEK, QUARTER, EPOCH があります。

SELECT EXTRACT(EPOCH FROM (NOW() - created_at)) AS seconds_ago
FROM events;

DATE_TRUNC：境界にそろえる

DATE_TRUNC(unit, ts) は、指定した単位より下位の要素をすべてゼロにします。区切りごとのレポートに最も便利な関数です。

SELECT date_trunc('day',   NOW());   -- 2024-03-15 00:00:00
SELECT date_trunc('week',  NOW());
SELECT date_trunc('month', NOW());
SELECT date_trunc('hour',  NOW());

区切りごとのレポート

date_truncを使って月ごとの収益を集計します。

SELECT date_trunc('month', created_at) AS month,
       SUM(total) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

年初来／月初来

当期と前年の同じ時点を比較します。

-- This month so far
SELECT SUM(total) FROM orders
WHERE created_at >= date_trunc('month', NOW())
  AND created_at <  date_trunc('month', NOW()) + INTERVAL '1 month';

AGE：暦に基づく差分

AGEは日数だけでなく、年・月・日を返します。

SELECT AGE('2024-03-15', '2020-12-01');
-- → '3 years 3 months 14 days'

SELECT EXTRACT(YEAR FROM AGE('2024-03-15', '2020-12-01'));  -- 3

2引数のAGEの動作

引数を1つだけ指定したAGEは、current_date と比較します。

SELECT AGE(birth_date) FROM users LIMIT 5;

曜日の処理

EXTRACT(DOW)：0=日曜日 … 6=土曜日。ISO_DOW：1=月曜日 … 7=日曜日です。

SELECT * FROM orders
WHERE EXTRACT(ISODOW FROM created_at) IN (6, 7);  -- weekend orders

EXTRACTでのタイムゾーン

TIMESTAMPTZに対するEXTRACTは、セッションのタイムゾーンを使用します。タイムゾーンを制御するには AT TIME ZONE を使用します。

SELECT EXTRACT(HOUR FROM created_at AT TIME ZONE 'UTC') FROM events;
SELECT EXTRACT(HOUR FROM created_at AT TIME ZONE 'Europe/Berlin') FROM events;

独自の区切り幅で切り捨てる

5分ごとのような暦に基づかない区切りには、算術演算を使用します。

SELECT to_timestamp(
  floor(EXTRACT(EPOCH FROM created_at) / 300) * 300
) AS bucket_5min,
COUNT(*)
FROM events
GROUP BY 1
ORDER BY 1;

切り捨て用の関数インデックス

date_trunc('day', created_at) で頻繁にGROUP BYする場合は、関数インデックスが役立ちます。

CREATE INDEX events_day_idx ON events ((date_trunc('day', created_at)));

振り返り

EXTRACT、DATE_TRUNC、AGEで、ほとんどのレポート要件に対応できます。

  • 個々の要素にはEXTRACT
  • 境界にそろえるにはDATE_TRUNC
  • 暦に基づく期間にはAGE

確認問題

新規ユーザー数を月ごとに集計したいとします。日付を月単位にまとめる関数はどれですか？

よくある質問

「EXTRACT、DATE_TRUNC、AGE」レッスンは無料ですか？

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

「EXTRACT、DATE_TRUNC、AGE」で何を学びますか？

EXTRACTで日付の一部を取り出し、DATE_TRUNCでタイムスタンプを境界に切りそろえ、AGEで人間に読みやすい差分を計算します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「EXTRACT、DATE_TRUNC、AGE」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

  1. 日付計算とINTERVAL
  2. EXTRACT、DATE_TRUNC、AGE
  3. タイムゾーン：TIMESTAMPとTIMESTAMPTZ
  4. よくある日付レポート（MTD、WoW、YoY）
← SQL Academyに戻る