Lesson 5 of 5 · reading 15–25 minutes; practice and project time is on the module page

Money, time and IDs

🎯You'll be able to

Choose column types for money, dates and identifiers that avoid the classic rounding, time-zone and guessing bugs.

🏠The everyday picture

A shopkeeper counts change in coins, not in "roughly 3.7 rupees". A train ticket shows the departure in Indian time, but the railway plans on one clock everywhere. And a bank locker number can't be guessed from the one next to it.

📘What it really is

Money: store whole paise in an integer or bigint (total_paise = 26000 for ₹260.00), or use numeric with fixed places. Never use floating point (real, double, JavaScript arithmetic on decimals): 0.1 + 0.2 isn't exactly 0.3. Store the currency if you handle more than one.
Time: store instants as timestamptz (PostgreSQL keeps them in UTC). Convert to Asia/Kolkata only for display or for business rules like "orders before 10 am today". A day boundary in India (00:00 IST) is 18:30 UTC the day before.
Dates without time (a delivery day, a birthday) are date.
IDs:
bigint identity columns are compact and fast, but sequential, so /orders/120043 hints at /orders/120042;
UUIDs (random, or the time-ordered UUIDv7) are hard to guess and can be created by any service without asking the database.
Either way, guessable IDs are only safe if every request checks ownership (BE6).

📝A worked example

Input: an order of 3 dishes at ₹49.99, ₹120.00 and ₹15.50, with 5% GST, placed at 2026-11-01 23:50 IST.

Steps:

Prices in paise: 4999 + 12000 + 1550 = 18549.
GST in paise: 18549 × 5 / 100 = 927.45, rounded half up to 927 (state your rounding rule and use it everywhere).
Total: 18549 + 927 = 19476 paise = ₹194.76.
Stored time: 2026-11-01T18:20:00Z (UTC). For "which day's capacity?", convert: placed_at AT TIME ZONE 'Asia/Kolkata' → 1 November, 23:50, so it counts towards 1 November, not 2 November.

Expected output: total_paise = 19476, placed_at = 2026-11-01 18:20:00+00, delivery-day logic uses 1 November.

🌍Where it's used

Billing, invoicing and GST, where a paisa off on thousands of invoices fails reconciliation.
Scheduling and reporting across midnight in IST.
Public IDs in URLs, where guessable numbers invite snooping.

📝Case scenario

A report of "today's orders" uses WHERE placed_at::date = current_date on a server set to UTC. Every night, orders placed between 00:00 and 05:30 IST land in "yesterday", and the kitchen prepares too few tiffins for the morning. Computing the day in Asia/Kolkata, as the kit's menu query does, fixes it.

🖱️Try it

In psql, run SELECT 0.1::float8 + 0.2::float8 = 0.3::float8; and SELECT 10 + 20 = 30;. Then run SELECT '2026-11-01 23:50+05:30'::timestamptz AT TIME ZONE 'UTC', ('2026-11-01 23:50+05:30'::timestamptz AT TIME ZONE 'Asia/Kolkata')::date;. Success: you can explain each result in one sentence.

⚠️Common mistakes

Floats for money. Sign: totals off by a paisa, or 0.30000000000000004. Fix: integers in paise, with one rounding rule.
Local time without a zone. Sign: orders "move" days around midnight IST, or shift 5 h 30 min. Fix: timestamptz, converted only at the edges.
Trusting hard-to-guess IDs as security. Sign: no ownership check because "nobody can guess a UUID". Fix: check ownership on every request; IDs leak through links and logs.

🛠️For your project

Project 1 test 10 checks the server computes the total in paise from the database's prices; Project 2's 30-day count uses the IST day boundary.

✅Quick check

Why store money as paise in an integer?

Show the answer

Integer arithmetic is exact, so totals and taxes add up to the paisa, unlike floating point.

An order is placed at 00:15 IST on 2 November. What UTC date and time is that?

Show the answer

18:45 UTC on 1 November.