Dates, intervals and the two kinds of timestamp

Adding a month is not one operation

What is 31 January plus one month? Postgres says 28 February. Add another month and it says 28 March, not 31 March, because the intermediate result has already lost the 31. Month arithmetic is not associative: '2026-01-31' + interval '2 months' is 31 March, but ('2026-01-31' + interval '1 month') + interval '1 month' is 28 March.

SQLite makes a different choice. date('2026-01-31', '+1 month') produces 2026-03-03, because it adds one to the month number to get 31 February, then normalises the overflow forward into March. Neither engine is wrong; each has picked a rule, and a schedule that says “bill on the same day next month” has to pick one too, and write it down.

The safe patterns: work from the first of the month and add months to that, or anchor to “the last day of the month” explicitly with date_trunc('month', d) + interval '1 month' - interval '1 day'. Both survive being applied repeatedly.

Days between two dates

Subtracting two date values in Postgres gives an integer number of days. Subtracting two timestamps gives an interval, which prints as 3 days 04:12:00 and has to be converted to be summed or averaged: extract(epoch FROM (b - a)) / 86400 gives fractional days. age(b, a) gives a human-shaped interval in years, months and days, which is right for “how old is this account” on a screen and wrong for arithmetic, because a month has no fixed length. DuckDB’s date_diff('day', a, b) and SQLite’s julianday(b) - julianday(a) are the equivalents.

timestamp and timestamptz are different types

Postgres has both, and the names mislead. timestamp (without time zone) is a wall-clock reading with no information about where the clock was: 2026-03-29 02:30:00 and nothing more. timestamptz (with time zone) is an instant, stored internally as UTC and converted to the session’s time zone on the way out. The second one is nearly always what you want, because an instant can be compared with any other instant, while a wall-clock reading from Mumbai and one from London cannot be ordered without knowing which is which.

The trap is that both accept the same input text, so a column created as timestamp will happily store values that were actually instants, quietly discarding the zone. A database that received 2026-03-29T02:30:00+05:30 into a timestamp column has kept 02:30 and thrown away the +05:30, and no query can get it back.

The rule that follows: store timestamptz, keep the server’s session zone at UTC, and convert for display with AT TIME ZONE 'Asia/Kolkata'. Use the named zone, never a fixed offset like +05:30, because named zones carry the history of when a region changed its rules and an offset does not.

Daylight saving, for the readers who have it

India has no daylight saving, so a business there can go years without meeting this. A business with users in Europe or North America meets it twice a year. On 29 March 2026 the clocks in London go from 00:59:59 GMT to 02:00:00 BST, so the wall-clock time 01:30 does not exist that day, and on the last Sunday of October 01:30 happens twice. A timestamp column cannot represent which of the two it means; a timestamptz can, because it stores the instant. Daily totals grouped in local time will have one 23-hour day and one 25-hour day a year, and a report that compares those days with their neighbours will show a dip and a bump that are calendar, not business.

The clock inside a transaction

now() in Postgres returns the time the current transaction started, and returns the same value for every call inside it, however long it runs. A batch that stamps ten thousand rows with now() over four minutes gives them all the same timestamp. clock_timestamp() reads the real clock each time. Most of the time the frozen value is what you want, because it makes a transaction’s writes look simultaneous; for measuring how long steps took, it is useless.

Store what you were given

When data arrives with a zone, keep it. When it arrives without one, record where it came from in a second column, because “the export was in the branch’s local time” is a fact that lives in someone’s head until the day they leave. Everything in the earlier lesson on grouping by time depends on knowing whose midnight a value refers to, and that knowledge has to be written down somewhere the query can reach.