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.
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:
| txn_time | txn_month | txn_hour |
|---|---|---|
| 2026-01-05T08:12:00.000Z | 1 | 8 |
| 2026-02-21T12:30:00.000Z | 2 | 12 |
Pulling out month and hour
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:
| txn_time | month_start |
|---|---|
| 2026-01-05T08:12:00.000Z | 2026-01-01T00:00:00.000Z |
| 2026-01-18T17:40:00.000Z | 2026-01-01T00:00:00.000Z |
| 2026-02-02T07:05:00.000Z | 2026-02-01T00:00:00.000Z |
Rounding down to the month
Group by the truncated value
Combine DATE_TRUNC with GROUP BY for a monthly report: the pattern behind almost every "revenue by month" chart:
| month_start | total_amount |
|---|---|
| 2026-01-01T00:00:00.000Z | 145.50 |
| 2026-02-01T00:00:00.000Z | 162.00 |
Monthly totals
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:
| txn_date | days_before_march |
|---|---|
| 2026-01-05T00:00:00.000Z | 55 |
| 2026-02-21T00:00:00.000Z | 8 |
Days until a fixed date
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 -:
| txn_time | follow_up_by | two_hours_earlier |
|---|---|---|
| 2026-01-05T08:12:00.000Z | 2026-01-12T08:12:00.000Z | 2026-01-05T06:12:00.000Z |
Shifting timestamps forward and backward
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:
| format | example output |
|---|---|
'DD Mon YYYY' | 05 Jan 2026 |
'HH24:MI' | 08:12 |
This is purely for display; keep using the real timestamp for any filtering or math.
Human-readable date and time
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.