EXTRACT, DATE_TRUNC, AGE
Pull out parts of a date with EXTRACT, snap timestamps to a boundary with DATE_TRUNC, and compute human-readable diffs with AGE.
EXTRACT, DATE_TRUNC, AGE is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
EXTRACT: Pull Out a Date Part
EXTRACT(part FROM ts) returns a single numeric component:
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;Common EXTRACT Parts
Useful parts: 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: Snap to a Boundary
DATE_TRUNC(unit, ts) zeros everything below the unit — the most useful function for bucketed reports:
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());Bucketed Reports
Monthly revenue using date_trunc:
SELECT date_trunc('month', created_at) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;Year-to-Date / Month-to-Date
Compare current period vs same point last year:
-- 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: Calendar-Aware Difference
AGE returns years + months + days, not just days:
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')); -- 3Two-Arg AGE Behaviour
Single-argument AGE compares to current_date:
SELECT AGE(birth_date) FROM users LIMIT 5;Day-of-Week Logic
EXTRACT(DOW): 0=Sunday … 6=Saturday. ISO_DOW: 1=Monday … 7=Sunday.
SELECT * FROM orders
WHERE EXTRACT(ISODOW FROM created_at) IN (6, 7); -- weekend ordersTime Zone in EXTRACT
EXTRACT on TIMESTAMPTZ uses the session's time zone. Use AT TIME ZONE to control it:
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;Truncate by Custom Window
For non-calendar windows (every 5 minutes), use arithmetic:
SELECT to_timestamp(
floor(EXTRACT(EPOCH FROM created_at) / 300) * 300
) AS bucket_5min,
COUNT(*)
FROM events
GROUP BY 1
ORDER BY 1;Functional Indexes for Truncation
If you GROUP BY date_trunc('day', created_at) a lot, a functional index helps:
CREATE INDEX events_day_idx ON events ((date_trunc('day', created_at)));Recap
EXTRACT + DATE_TRUNC + AGE cover most reporting needs.
- EXTRACT for individual parts
- DATE_TRUNC to snap to a boundary
- AGE for calendar-aware spans
Quick Check
You want a monthly count of new users. Which function buckets dates by month?
Frequently asked questions
Is the “EXTRACT, DATE_TRUNC, AGE” lesson free?
Yes — the full text of “EXTRACT, DATE_TRUNC, AGE” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “EXTRACT, DATE_TRUNC, AGE”?
Pull out parts of a date with EXTRACT, snap timestamps to a boundary with DATE_TRUNC, and compute human-readable diffs with AGE. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “EXTRACT, DATE_TRUNC, AGE” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.