Lesson 20: Date & Time Functions

Date functions let you filter, group, and calculate durations directly in SQL — critical for anything time-series or reporting related.

Real-time example

A subscription business reports monthly signups and flags accounts inactive for over 30 days.

SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS signups
FROM customers
GROUP BY month
ORDER BY month;

SELECT name FROM customers
WHERE last_login < now() - interval '30 days';

What's happening

DATE_TRUNC rounds a timestamp down to the given unit (month, day, hour) — the standard way to bucket time-series data for grouping.

Store timestamps in UTC and convert to local time only at display time — mixing time zones in stored data causes subtle, hard-to-find bugs.