Chapter 13 · basic · 7 min
Date & time functions
Almost every real analytics question has a time dimension: "per month", "in the last 7 days", "how long until X". Postgres timestamps are truly arithmetic: you can subtract, truncate, and pull out individual parts.
We'll roll up individual fuel transactions into monthly totals.
The dataset
A transactions table: each row is one purchase with a precise timestamp.
Schema
| id | INT |
| customer | TEXT |
| amount | NUMERIC |
| txn_time | TIMESTAMP |
Example data
EXTRACT: pull out one part
EXTRACT(part FROM timestamp) pulls a single component: year, month, day, hour, and more. Useful when you want to group or filter by just one part of a date.
Before you run it — Bala's transaction is 2026-01-18 17:40:00. What month and hour do you expect?
DATE_TRUNC: round down to a unit
DATE_TRUNC(unit, timestamp) rounds a timestamp down to the start of that unit: the start of its month, week, day, etc. This is the standard way to bucket timestamps for a GROUP BY, since two timestamps in the same month truncate to the same value.
Before you run it — Aisyah and Bala are both January transactions. Do their month_start values come out identical?
Group by the truncated value
Combine DATE_TRUNC with GROUP BY for a monthly report: the pattern behind almost every "revenue by month" chart.
Before you run it — Aisyah (80.00) + Bala (65.50) are January. What January total do you expect?
Date arithmetic
Subtracting two DATEs (or the date part of a timestamp) gives you a plain integer: the number of days between them. This is how you compute ages, durations, and days-until-expiry without any special function.
Before you run it — Devi's transaction is 2026-02-21. Roughly how many days before March 1st is that?
INTERVAL: shifting a timestamp
Where plain subtraction compares two existing timestamps, INTERVAL lets you shift a timestamp by a fixed amount: add a follow-up window, compute a deadline, or roll back to "this time yesterday". Add with +, go backward with -.
Before you run it — Aisyah's transaction is 2026-01-05 08:12:00. What's 7 days later, and 2 hours earlier?
TO_CHAR: formatting for display
Raw timestamps aren't how a report should look. TO_CHAR(timestamp, format) renders a date using a format string. This is purely for display; keep using the real timestamp for any filtering or math.
Before you run it — Chong's transaction is 2026-02-02 07:05:00. What pretty_date and pretty_time do you expect?
If you've used Excel or Google Sheets
EXTRACT(month FROM ...) is the same job as MONTH() (and YEAR(), DAY(), HOUR() for the rest). Plain date subtraction works exactly the same way in both: subtracting one date cell from another gives you a day count in Excel too, no special function needed. TO_CHAR is the equivalent of Excel/Sheets' TEXT(date, format). The one thing without a clean spreadsheet parallel is DATE_TRUNC. The closest everyday equivalent is grouping dates by month inside a PivotTable, except DATE_TRUNC gives you the bucketed value as real data you can GROUP BY directly.
Ready to practice? Setel's monthly fuel transactions question below is exactly this DATE_TRUNC + GROUP BY pattern.
Master EXTRACT, DATE_TRUNC, date/interval arithmetic, and TO_CHAR, and you can answer almost any time-series interview question: compute it with the real timestamp, format it with TO_CHAR only at the very end for display. That wraps up the single-table basics; next we start combining and summarizing data, starting with aggregation.