SQL date functions in PostgreSQL: date_trunc, intervals and date math
Most analytics questions are about time: per month, within 7 days, since signup. Postgres makes date math direct once you know a handful of functions.
Subtracting dates
date - date gives a whole number of days. date + 7 is a week later, and date + INTERVAL '1 month' moves by calendar months.
SELECT name, CURRENT_DATE - signup_date AS days_since_signup FROM customers;
Grouping by month
date_trunc('month', d) rounds a date down to the first of its month, which is what you GROUP BY. to_char(d, 'YYYY-MM') turns it into a label like 2024-03.
SELECT to_char(signup_date, 'YYYY-MM') AS month, COUNT(*) AS signups FROM customers GROUP BY 1 ORDER BY 1;
Parts of a date
EXTRACT(YEAR FROM d), EXTRACT(MONTH FROM d) and EXTRACT(ISODOW FROM d) (1 = Monday to 7 = Sunday) pull out single parts.
Try it
This runs real PostgreSQL in your browser on the practice tables. Change it and run it again.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Common mistakes
- Functions from other databases: DATEDIFF, julianday, strftime, DATE_FORMAT.
- Comparing a date to '2024-07-31' with < when the last day should be included.
- Grouping by EXTRACT(MONTH) alone, which merges January 2024 and January 2025.
Practice drills
Reading it isn't enough. Solve these and it'll still be there in an interview.
- First-week shipmentsIN · BETWEENFree
- Weekend loginsEXTRACT · day of weekFree
- Purchase date for every userLEFT JOIN · filter in ON vs WHEREFree
- Consecutive-day loginsLAG() · date arithmeticPro
- Days to first purchaseCTE · MIN · date mathPro
- Monthly spendto_char · GROUP BY monthPro
- Fast convertersJOIN + date differencePro
- Month-over-month growthCTE + LAG · growth ratePro
- Longest login streakGaps and islandsPro
- Days until next orderLEAD() · PARTITION BYPro
- Active in every monthCOUNT(DISTINCT month) · relational divisionPro
- Daily active users, zero days includedgenerate_series · LEFT JOIN · filling date gapsPro
- 7-day moving average revenueRANGE window frame · calendar moving averagePro
- DAU/MAU stickinessDistinct user-days · days in month · ratiosPro
- Month-1 retention by signup cohortCohorts · date_trunc · LEFT JOINPro
- Monthly churn rateAnti-join across months · churnPro
- New vs returning usersFirst-seen month · running distinct countPro
- Activity streaks as date rangesGaps and islands · duplicate daysPro
- Pricing to checkout within an hourFunnel with a time window · range joinPro