Modeling Time

Store instants in UTC with the zone, and convert at the edges. timestamptz does this properly: it stores a moment, and renders it in whatever zone you ask for.

Storing local time without a zone means the same value means different moments depending on who reads it, and during a daylight saving change one hour either repeats or does not exist. Both are real bugs that appear twice a year.

A date is not an instant. A birthday is a date: it is the same date everywhere, and giving it a time zone makes it wrong for somebody. An appointment is an instant.

Getting this backwards is why people occasionally see a birthday shift by a day depending on where they are.

Ranges are a thing Postgres does well. A booking has a start and an end, and two bookings for the same room must not overlap. That is expressible as an EXCLUDE constraint using a range type, which means the database refuses the double booking rather than your application trying to remember to check.