{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"date-arithmetic-and-time-zones","title":"Dates, intervals and the two kinds of timestamp","url":"https://addaly.com/learn/data-and-sql/date-arithmetic-and-time-zones","position":42,"of":66,"minutes":9,"answer":"Store timestamptz and convert with a named zone at display time, because a plain timestamp discards where the clock was, and treat month arithmetic as a rule you chose.","keyIdea":"Store timestamptz and convert with a named zone at display time, because a plain timestamp discards where the clock was, and treat month arithmetic as a rule you chose, since engines disagree on what 31 January plus a month is."},"body":{"format":"markdown","text":"## Adding a month is not one operation\n\nWhat 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.\n\nSQLite 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.\n\nThe 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.\n\n## Days between two dates\n\nSubtracting 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.\n\n## timestamp and timestamptz are different types\n\nPostgres 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.\n\nThe 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.\n\nThe 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.\n\n## Daylight saving, for the readers who have it\n\nIndia 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.\n\n## The clock inside a transaction\n\n`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.\n\n## Store what you were given\n\nWhen 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."},"check":{"question":"A subscription system stores each customer's next billing date by repeatedly adding one month to the previous one. A customer who first paid on 31 January is billed on 28 February, then 28 March, then 28 April. What explains the drift?","options":[{"text":"The database rounds every interval addition down to the nearest 28 days so that months are comparable, which permanently shortens the cycle.","correct":false,"tempting":"Intervals are not rounded to a fixed day count; the 28 came from February's length, not from a rounding rule."},{"text":"Each addition starts from the previous result, which lost the 31 when it hit February, so month arithmetic applied step by step is not the same as adding several months at once.","correct":true},{"text":"The billing column is a `timestamp` without time zone, so the +05:30 offset was discarded on every write and each cycle lost half a day until the drift added up to a whole day per month.","correct":false,"tempting":"An offset discarded at storage time is a one-off error of hours, not a three-day shift that appears only at the February boundary."},{"text":"`interval '1 month'` in Postgres is defined as 28 days for compatibility with SQLite, which normalises overflowing dates the same way.","correct":false,"tempting":"Postgres's month interval is a calendar month clamped to the month's last day, and SQLite normalises the other way, into the following month."}]},"prev":{"slug":"cohorts-and-retention","title":"Cohorts and retention: how a month of new customers behaves afterwards","url":"https://addaly.com/learn/data-and-sql/cohorts-and-retention"},"next":{"slug":"as-of-joins","title":"As-of joins: the value that was true at the time","url":"https://addaly.com/learn/data-and-sql/as-of-joins"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Dates, intervals and the two kinds of timestamp', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/date-arithmetic-and-time-zones (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Dates, intervals and the two kinds of timestamp","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":4,"slug":"windows-sequences-and-time","title":"Windows, sequences and time"},"answer":null,"keyIdea":"Store timestamptz and convert with a named zone at display time, because a plain timestamp discards where the clock was, and treat month arithmetic as a rule you chose, since engines disagree on what 31 January plus a month is.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"adding-a-month-is-not-one-operation","runs":[{"text":"Adding a month is not one operation"}]},{"t":"p","runs":[{"text":"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: "},{"text":"'2026-01-31' + interval '2 months'","code":true},{"text":" is 31 March, but "},{"text":"('2026-01-31' + interval '1 month') + interval '1 month'","code":true},{"text":" is 28 March."}]},{"t":"p","runs":[{"text":"SQLite makes a different choice. "},{"text":"date('2026-01-31', '+1 month')","code":true},{"text":" produces "},{"text":"2026-03-03","code":true},{"text":", 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."}]},{"t":"p","runs":[{"text":"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 "},{"text":"date_trunc('month', d) + interval '1 month' - interval '1 day'","code":true},{"text":". Both survive being applied repeatedly."}]},{"t":"h2","id":"days-between-two-dates","runs":[{"text":"Days between two dates"}]},{"t":"p","runs":[{"text":"Subtracting two "},{"text":"date","code":true},{"text":" values in Postgres gives an integer number of days. Subtracting two timestamps gives an "},{"text":"interval","code":true},{"text":", which prints as "},{"text":"3 days 04:12:00","code":true},{"text":" and has to be converted to be summed or averaged: "},{"text":"extract(epoch FROM (b - a)) / 86400","code":true},{"text":" gives fractional days. "},{"text":"age(b, a)","code":true},{"text":" 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 "},{"text":"date_diff('day', a, b)","code":true},{"text":" and SQLite’s "},{"text":"julianday(b) - julianday(a)","code":true},{"text":" are the equivalents."}]},{"t":"h2","id":"timestamp-and-timestamptz-are-different-types","runs":[{"text":"timestamp and timestamptz are different types"}]},{"t":"p","runs":[{"text":"Postgres has both, and the names mislead. "},{"text":"timestamp","code":true},{"text":" (without time zone) is a wall-clock reading with no information about where the clock was: "},{"text":"2026-03-29 02:30:00","code":true},{"text":" and nothing more. "},{"text":"timestamptz","code":true},{"text":" (with time zone) is an "},{"text":"instant","i":true},{"text":", 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."}]},{"t":"p","runs":[{"text":"The trap is that both accept the same input text, so a column created as "},{"text":"timestamp","code":true},{"text":" will happily store values that were actually instants, quietly discarding the zone. A database that received "},{"text":"2026-03-29T02:30:00+05:30","code":true},{"text":" into a "},{"text":"timestamp","code":true},{"text":" column has kept "},{"text":"02:30","code":true},{"text":" and thrown away the "},{"text":"+05:30","code":true},{"text":", and no query can get it back."}]},{"t":"p","runs":[{"text":"The rule that follows: store "},{"text":"timestamptz","code":true},{"text":", keep the server’s session zone at UTC, and convert for display with "},{"text":"AT TIME ZONE 'Asia/Kolkata'","code":true},{"text":". Use the named zone, never a fixed offset like "},{"text":"+05:30","code":true},{"text":", because named zones carry the history of when a region changed its rules and an offset does not."}]},{"t":"h2","id":"daylight-saving-for-the-readers-who-have-it","runs":[{"text":"Daylight saving, for the readers who have it"}]},{"t":"p","runs":[{"text":"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 "},{"text":"01:30","code":true},{"text":" does not exist that day, and on the last Sunday of October "},{"text":"01:30","code":true},{"text":" happens twice. A "},{"text":"timestamp","code":true},{"text":" column cannot represent which of the two it means; a "},{"text":"timestamptz","code":true},{"text":" 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."}]},{"t":"h2","id":"the-clock-inside-a-transaction","runs":[{"text":"The clock inside a transaction"}]},{"t":"p","runs":[{"text":"now()","code":true},{"text":" 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 "},{"text":"now()","code":true},{"text":" over four minutes gives them all the same timestamp. "},{"text":"clock_timestamp()","code":true},{"text":" 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."}]},{"t":"h2","id":"store-what-you-were-given","runs":[{"text":"Store what you were given"}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A subscription system stores each customer’s next billing date by repeatedly adding one month to the previous one. A customer who first paid on 31 January is billed on 28 February, then 28 March, then 28 April. What explains the drift?"}],"options":[{"text":[{"text":"The database rounds every interval addition down to the nearest 28 days so that months are comparable, which permanently shortens the cycle."}],"correct":false,"tempting":[{"text":"Intervals are not rounded to a fixed day count; the 28 came from February’s length, not from a rounding rule."}]},{"text":[{"text":"Each addition starts from the previous result, which lost the 31 when it hit February, so month arithmetic applied step by step is not the same as adding several months at once."}],"correct":true,"tempting":null},{"text":[{"text":"The billing column is a "},{"text":"timestamp","code":true},{"text":" without time zone, so the +05:30 offset was discarded on every write and each cycle lost half a day until the drift added up to a whole day per month."}],"correct":false,"tempting":[{"text":"An offset discarded at storage time is a one-off error of hours, not a three-day shift that appears only at the February boundary."}]},{"text":[{"text":"interval '1 month'","code":true},{"text":" in Postgres is defined as 28 days for compatibility with SQLite, which normalises overflowing dates the same way."}],"correct":false,"tempting":[{"text":"Postgres’s month interval is a calendar month clamped to the month’s last day, and SQLite normalises the other way, into the following month."}]}],"order":[2,1,3,0],"why":null},"prev":{"slug":"cohorts-and-retention","title":"Cohorts and retention: how a month of new customers behaves afterwards","minutes":10},"next":{"slug":"as-of-joins","title":"As-of joins: the value that was true at the time","minutes":10}}}