SQL DATE_TRUNC by Year

Rounds down to January 1st of the same year — useful for grouping records into yearly buckets.

What & Why

DATE_TRUNC('year', date_column) zeroes out everything except the year, landing on January 1st at midnight. Grouping by this expression buckets every record into its calendar year.

See How It Works

BUSINESS QUESTION

Growth wants annual signup totals.

id	created_at	country	channel	plan	activated_at	churned_at
101	2024-01-15 09:10:00+00	US	organic	pro	2024-01-16 14:25:00+00	NULL
102	2024-02-10 11:05:00+00	CA	paid	free	NULL	2024-03-20 10:00:00+00
103	2024-03-12 16:35:00+00	GB	referral	pro	2024-03-13 08:15:00+00	NULL
104	2024-04-01 13:20:00+00	US	organic	free	2024-04-03 12:00:00+00	NULL

EXAMPLE QUERY

SELECT
  DATE_TRUNC('year', created_at)::date AS signup_year,
  COUNT(*) AS signups
FROM growth.users
GROUP BY signup_year
ORDER BY signup_year;

RESULT — exact output from the displayed Queryflo rows

signup_year	signups
2024-01-01	4

DATE_TRUNC maps every displayed signup to the same year boundary.

Now You Try

Practice this concept

Growth wants annual signup totals.

Available schema

growth

Prefix tables with growth.table_name.

signup_yearsignups

growth.users

Column	Type
id	integer
created_at	timestamp with time zone
country	text
channel	text
plan	text
activated_at	timestamp with time zone
churned_at	timestamp with time zone
legacy_user_code	text

query.sql

Intermediate business practice