What is the difference between DATE_TRUNC and EXTRACT?

EXTRACT returns a single numeric field (like the month number, 5) and drops the rest of the date. DATE_TRUNC keeps a full timestamp but rounds it down to the start of a period (like 2026-05-01 00:00:00). Use EXTRACT to pull a part out, and DATE_TRUNC to group rows into time buckets.

How do I get rows from the last 7 days in SQL?

Use an interval bound: WHERE created_at >= NOW() - INTERVAL '7 days'. Keep the column bare on the left so PostgreSQL can use an index on created_at, rather than wrapping it in a function.

How do I group by month in PostgreSQL?

Use DATE_TRUNC('month', created_at) in both SELECT and GROUP BY: SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) FROM orders GROUP BY month ORDER BY month. This buckets every row into the first day of its month.

Intermediate

SQL Date Functions: DATE_TRUNC, EXTRACT & Intervals

Working with dates trips up almost everyone. A practical guide to the PostgreSQL date toolkit — grouping by month, date math with intervals, and time-zone-safe queries.

Dates are deceptively hard. The moment you need "revenue per month" or "users who signed up in the last 30 days", you are reaching for date functions — and the syntax differs across databases. This guide covers the PostgreSQL toolkit, which is one of the richest and most consistent. Bookmark the SQL cheat sheet for quick reference.

Current Date & Time

Start with the values the database gives you for "now". Each returns a slightly different type.

SELECT
  NOW() AS timestamp_now,       -- 2026-05-29 14:30:00+00
  CURRENT_DATE AS today,        -- 2026-05-29
  CURRENT_TIME AS time_now,     -- 14:30:00+00
  CURRENT_TIMESTAMP AS ts;      -- same as NOW()

EXTRACT — Pull Out Parts

EXTRACT pulls a single field — year, month, day, hour, day-of-week — out of a date or timestamp. It is how you answer questions like "how many orders happened on weekends?"

SELECT
  EXTRACT(YEAR  FROM created_at) AS yr,
  EXTRACT(MONTH FROM created_at) AS mon,
  EXTRACT(DOW   FROM created_at) AS day_of_week  -- 0 = Sunday
FROM orders;

-- Orders placed on a weekend
SELECT COUNT(*)
FROM orders
WHERE EXTRACT(DOW FROM created_at) IN (0, 6);

DATE_TRUNC — Group by Period

DATE_TRUNC is the single most useful date function for analytics. It rounds a timestamp down to the start of a period — month, week, day, hour — so you can GROUP BY it. This is how every "per month" chart is built.

-- Monthly revenue
SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(total) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

DATE_TRUNC('month') — DATE_TRUNC rounds each timestamp down to the start of its period, so rows from the same month collapse into one bucket you can GROUP BY.

Interval Math

You can add and subtract time using INTERVAL. This is the clean way to write "the last 30 days" or "expires in 14 days" without hard-coding dates.

-- Signups in the last 30 days
SELECT *
FROM users
WHERE created_at >= NOW() - INTERVAL '30 days';

-- Add time to a date
SELECT
  created_at,
  created_at + INTERVAL '14 days' AS trial_ends
FROM users;

AGE & Date Differences

To measure the gap between two dates, AGE gives a human-readable interval, while plain subtraction gives you days (for dates) or an interval (for timestamps).

-- Human-readable account age
SELECT name, AGE(NOW(), created_at) AS account_age
FROM users;

-- Days between two dates
SELECT order_id,
       (shipped_at::date - ordered_at::date) AS days_to_ship
FROM orders;

Formatting Dates

TO_CHAR turns a date into a formatted string for display. Use it for output only — never store or filter on formatted strings, because that throws away the date type and any index with it.

SELECT
  TO_CHAR(created_at, 'YYYY-MM-DD') AS iso_date,
  TO_CHAR(created_at, 'Mon DD, YYYY') AS pretty,   -- May 29, 2026
  TO_CHAR(created_at, 'Day') AS weekday             -- Friday
FROM orders;

Common Mistakes & Tips

Wrapping the date column in a function in WHERE — it blocks index use; compare against a computed bound instead
Using BETWEEN with timestamps — the upper bound at midnight excludes most of the last day; use >= start AND < next_day
Forgetting time zones — timestamptz and timestamp compare differently; be explicit
Filtering on TO_CHAR output — you lose the date type and the index
Assuming EXTRACT(DOW) starts on Monday — in PostgreSQL 0 is Sunday

Date logic pairs constantly with GROUP BY and aggregates. Put them together and practice on the banking dataset, which has real timestamped transactions.