PostgreSQL Date and Time Functions

Getting the Current Date/Time

SELECT now();               -- current timestamp with timezone
SELECT CURRENT_DATE;        -- just today's date
SELECT CURRENT_TIME;        -- just the current time

EXTRACT — Pull Out a Component

SELECT EXTRACT(YEAR FROM hired_at) FROM employees;
SELECT EXTRACT(MONTH FROM hired_at) FROM employees;
SELECT EXTRACT(DOW FROM hired_at) FROM employees;
-- DOW = day of week, 0 (Sunday) through 6 (Saturday)

date_trunc — Round Down to a Period

Essential for grouping records by day/month/year, since two timestamps in the same month rarely match exactly.

SELECT date_trunc('month', hired_at), COUNT(*)
FROM employees
GROUP BY date_trunc('month', hired_at)
ORDER BY 1;
-- how many employees were hired each month

SELECT date_trunc('day', created_at), SUM(total)
FROM orders
GROUP BY 1
ORDER BY 1;
-- daily revenue totals

Date Arithmetic

SELECT hired_at + INTERVAL '30 days' FROM employees;
SELECT hired_at - INTERVAL '1 year' FROM employees;

SELECT now() - hired_at AS tenure FROM employees;
-- an INTERVAL value, e.g. "365 days 04:12:33"

AGE() — Human-Readable Duration

SELECT name, AGE(now(), hired_at) AS tenure FROM employees;
-- e.g. "1 year 2 mons 15 days" — friendlier than a raw interval

Formatting Dates

SELECT TO_CHAR(hired_at, 'DD Mon YYYY') FROM employees;
-- '15 Jun 2024'

SELECT TO_CHAR(hired_at, 'YYYY-MM') FROM employees;
-- '2024-06'

generate_series — Building a Time Series

Generates every value in a range — invaluable for filling in dates that have zero rows (like a day with no sales), which a plain GROUP BY would silently skip.

SELECT day::DATE
FROM generate_series('2024-06-01', '2024-06-30', INTERVAL '1 day') AS day;

SELECT d.day::DATE, COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series('2024-06-01', '2024-06-30', INTERVAL '1 day') AS d(day)
LEFT JOIN orders o ON date_trunc('day', o.created_at) = d.day
GROUP BY d.day
ORDER BY d.day;
-- every day in June appears, even ones with $0 in sales

Common Errors & Troubleshooting

• GROUP BY hired_at producing one row per employee instead of per month — grouping directly on a raw timestamp rarely collapses anything, since exact times rarely match; group on date_trunc('month', hired_at) instead.
• Timezone confusion between TIMESTAMP and TIMESTAMPTZ — now() returns a TIMESTAMPTZ; comparing it against a plain TIMESTAMP column applies the session's timezone implicitly, which can silently shift results — keep both sides the same type where possible.
• A day with zero rows missing entirely from a report — a plain GROUP BY on real data only produces rows for dates that actually occurred; use generate_series() (shown above) joined with LEFT JOIN to force every day to appear, even with 0 values.

Key Takeaways

• date_trunc() is the standard way to group timestamps by day/week/month/year — comparing raw timestamps rarely matches.
• AGE() gives a friendly "N years M months D days" style interval, better for display than a raw subtraction.
• generate_series() fills gaps a plain GROUP BY would leave empty — critical for time-series reports that must show zero-activity periods too.