Time is the area where reasonable-looking code is most often subtly wrong, and where the wrongness surfaces long after release - a report covering the wrong window, a subscription charged a day early, a scheduled job that runs twice or not at all on one particular Sunday. Indian teams have a slight blind spot here, because India has a single time zone and no daylight saving, so local testing never reveals the problems.
The base rule: store instants in UTC, convert only at display. A timestamp column with a time zone, holding the moment something happened, is unambiguous forever. Storing local time without an offset means that a year later nobody can determine what moment it referred to, and the ambiguity is unrecoverable.
The rule has an important exception that catches people who learned only the first half. Future events specified in local terms - a meeting at nine in the morning next March, a recurring monthly report - must store the local time and the time zone identifier, not a converted UTC instant. Governments change time zone rules, including at short notice. If you converted to UTC in advance, your meeting silently moves by an hour; if you stored the local time and the zone, it stays at nine.
Always use time zone identifiers such as Asia/Kolkata or Europe/London, never fixed offsets. The offset is a property of a moment, not of a place: London is +00:00 in January and +01:00 in July. Storing an offset means the data is right for the day it was written and wrong for half the year.
The specific failure to know about is that not every local time exists, and some occur twice. When clocks spring forward, an hour of local time does not exist, and a job scheduled inside it either does not run or runs immediately after. When they fall back, an hour repeats, and a job can run twice - which for anything that charges money or sends email matters enormously. Idempotency in scheduled work is the practical defence, and it is the reason critical schedules are best expressed in UTC even when the business thinks in local time.
Two smaller things that cause a surprising share of real bugs. Date-only values - a birthday, an invoice date - are not instants and should be stored as dates, not timestamps, or they will shift across the date line when converted. And date ranges should be half-open: greater than or equal to the start, strictly less than the end. Using BETWEEN on timestamps either misses the final second of the period or double-counts a boundary row, which is why two reports of the same month disagree by one order.
