SQL DATE_TRUNC

What & Why

DATE_TRUNC('unit', source) rounds a timestamp down to the beginning of whatever unit you specify — truncating to 'month' zeroes out the day and time, landing on the 1st of that month at midnight. This is genuinely different from EXTRACT: EXTRACT pulls one number out and discards everything else; DATE_TRUNC keeps a full date/timestamp value, just rounded down.

See How It Works

SELECT
  id,
  created_at,
  DATE_TRUNC('year', created_at)::date AS signup_year,
  DATE_TRUNC('month', created_at)::date AS signup_month,
  DATE_TRUNC('day', created_at)::date AS signup_day
FROM marketing.leads
ORDER BY created_at, id;