EXTRACT, DATE_TRUNC and DATE_DIFF

Test dates show how the tests spread over the year and when a certificate runs out. Date functions turn raw days into months and ages you can count.

How it works

DATE_TRUNC rounds each date down to the first day of its month, quarter or year, so every date in one period becomes a single value to group on. DATE_DIFF compares two dates and counts how many boundaries of the unit lie between them.

Common mistake

expiry_date belongs to the certificate that a test issues. An average over every test mixes rows of different kinds, so filter to the tests you mean before you average.

Worked example

SELECT DATE_TRUNC(completed_date, QUARTER) AS test_quarter, COUNT(*) AS test_count
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY test_quarter
ORDER BY test_quarter

Every date is rounded down to the first day of its quarter, so each quarter becomes one group.

fact_mot_test is split by month, and the filter on completed_date keeps the read to the twelve months of 2024.

All BigQuery lessons