Date functions

Opening your lesson

Dates are numbers underneath, so you can compare, subtract and group them:

EXTRACT(MONTH FROM d) and EXTRACT(YEAR FROM d) pull out a part. dayname(d) gives the weekday's name.
DATE_TRUNC('month', d) rounds down to the first of the month, the usual way to group by month. Here (as in PostgreSQL) it returns a timestamp; add ::DATE for a plain date.
d2 - d1 gives the number of days between two dates. DATE_DIFF('day', d1, d2) does the same and also counts months or years.
d + INTERVAL 7 DAY moves a date. Adding one month to January 31 gives the last day of February.

Names vary: MySQL writes DATEDIFF(d2, d1) (days only) and DATE_ADD(d, INTERVAL 7 DAY); SQL Server writes DATEDIFF(day, d1, d2) and DATEADD(day, 7, d); PostgreSQL uses d2 - d1 and d + INTERVAL '7 days'.

When a date is missing because something hasn't happened yet, decide what to measure up to. Here, the data runs to June 30, 2025, so use that as "today" instead of the real current date.

SELECT DATE_TRUNC('month', order_date)::DATE AS month, COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

Orders per month: each date rounded down to the first of its month.

Choose your character

Your Work Gym account