One day is not 24 hours: date arithmetic that breaks quietly, measured on SQLite and D1

A traffic table I look after has one timestamp column and one job: answer "how many people came in the last 24 hours". I rewrote that query three times last week, and the three versions disagreed — not by a rounding error, but by a whole day.

The table was fine. The data was fine. The column is TEXT holding UTC timestamps, written by three code paths that each picked a different way to write the same instant. Here is what a single UTC day range does to that table, counted over 6,480 rows per format:

Same instants, three formats, one query, and two of them report that nothing happened. The reason fits in one line of ASCII: 'T' is 0x54 and a space is 0x20, so '2026-11-15T00:00:00Z' > '2026-11-15 23:59:59' is true in every SQL engine that compares strings byte by byte. And across that same 36 hours of data, a rolling 24-hour window returned 2,283 rows for the space format, 2,160 for the T/Z format, and 540 for the offset format — three answers, all silently wrong except one.

I spent a morning measuring this properly: what a day actually costs when it is not 24 hours long, which local times do not exist, where month arithmetic clamps, and how a real table's "best day" changes depending on which calendar you bucket it in. Everything below is measured, on SQLite 3.46.1, on Cloudflare D1, and on the 192-row traffic table that started this. Numbers are specific to these runs; the failure shapes are what travel.

How I measured

Three pieces, no framework:

1. Python 3.12.16 with zoneinfo against the system tz database — 598 zones available, so the transitions are the real ones, not approximations.
2. SQLite 3.46.1 in memory for the storage and comparison questions, plus EXPLAIN QUERY PLAN and the minimum of five timed runs for the index questions.
3. The production D1 database, read-only — SELECT only, no CREATE TABLE, no writes. Where a question could not be answered without writing, I say so instead of guessing.

The point of using a real table rather than a synthetic one is that the synthetic table never has three writers. Mine did, and that is the whole article in one sentence: a timestamp is a value, but a timestamp column is a text field, and three code paths can write three different strings for the same instant.

One calendar day is not 24 hours

Everyone knows about daylight saving time. What is less obvious is how quietly it changes arithmetic that does not look like it involves time zones at all:

"Add one day" and "add 24 hours" are the same operation for 363 days a year in New York and different operations on the other two. If your schedule says "every day at 09:00" and your code says now + 86400, it drifts by an hour twice a year and stays drifted until it drifts back. If your code says "the window is 24 hours" and you compute it as date + 1 in local time, it is a 23-hour window on one day of the year and nobody will notice until a monthly total is off.

My own product runs on Asia/Seoul, which has had no daylight saving since 1988, so none of this hurts me today — which is exactly why I would write the bug and find it in a year when a partner or a user is somewhere else. Seoul's immunity is a local accident, not a design decision.

Some local times do not exist, and some exist twice

On 2026-03-08 in New York, the clocks go from 02:00 to 03:00. Those 60 minutes of wall clock do not exist:

2026-03-08 02:00 fold=0 -> utcoffset -05:00 -> UTC 07:00 -> back to NY: 03:00 EDT
2026-03-08 02:30 fold=0 -> utcoffset -05:00 -> UTC 07:30 -> back to NY: 03:30 EDT
2026-03-08 02:59 fold=0 -> utcoffset -05:00 -> UTC 07:59 -> back to NY: 03:59 EDT

Note what the library did: it did not raise, it did not return None, it picked an offset and moved the value forward an hour. A schedule stored as "02:30 daily" fires at 03:30 on that day, once, forever unremarked.

On 2026-11-01, the same zone repeats 01:00 to 02:00, and the same wall clock string means two instants:

2026-11-01 01:30 fold=0 -> offset -04:00 -> UTC 05:30
2026-11-01 01:30 fold=1 -> offset -05:00 -> UTC 06:30

If your schema stores a local wall-clock time and a separate date, 2026-11-01 01:30 is ambiguous and your storage cannot express which one you meant. If it stores UTC, it can — which is the argument for storing UTC in one place, and the reason a local time is only safe as display or as an intent resolved at the moment of use.

Counting this for the whole of 2026, by testing every local minute of the year against its round trip:

Two of those zeros are not the same zero. Seoul and Kolkata simply never change. São Paulo used to change every year and abolished DST in 2019, so its zero is recent history — and code written against a 2018 assumption is still running somewhere with a fixed -02:00 in it. Chatham is in the table because its offset is +12:45/+13:45: a 45-minute offset is enough to break any code that assumes offsets come in whole hours.

"Same time every day" is not the same UTC hour every day

Take a fixed local hour and ask which UTC hour it lands on across 2026:

This table is the reason a scheduler cannot store a UTC hour and call it a daily job. It also cuts the other way: a cron at 0 0 * * * UTC is 09:00 in Seoul, and if you rename that to "daily 09:00" in your UI, the label is right until someone in London asks why their report arrives at 08:00 for half the year.

The fix is small and boring: store the zone name with the schedule, resolve the due time in that zone at every run. One VARCHAR and one line of arithmetic. What it buys is that "09:00" stays 09:00 for the human who agreed to it.

Storing an offset is not storing a zone

Asia/Seoul today is +09:00. It has not always been:

Asia/Seoul 1987-05-10 12:00 -> +10:00   (summer time)
Asia/Seoul 1988-05-08 12:00 -> +10:00   (summer time)
Asia/Seoul 1988-10-09 12:00 -> +09:00
Asia/Seoul 1908-04-01 12:00 -> +08:30
Asia/Seoul 2026-06-01 12:00 -> +09:00

Korea ran daylight saving in 1987 and 1988, and before 1912 its standard time was 30 minutes off. Any archive or invoice with a date before those transitions, converted with a hardcoded +09:00, is wrong by an hour or thirty minutes — and the error is invisible because the output looks like a normal timestamp. The tz database in this run carries 598 zones and their full histories; that is the file you want on your side if you ever convert a stored local time into an instant.

The practical rule I ended up with: store the instant in UTC, store the zone name where a wall clock matters, and never store an offset as if it were a zone. If you must keep a local time for display in a database that has no tz database, keep it next to the zone name, not a +09:00.

The text comparison problem, in full

SQLite and D1 have no date type. A timestamp is text, and every comparison you write is a string comparison unless you wrap both sides in a date function. That is not a bug in the engine; it is the contract. The bugs are all on our side:

Formatting is not normalised, so slicing by day returns zero. Measured on 6,480 rows per format, one UTC day range:

Rolling windows lose the same day's rows. A 24-hour window 2026-11-14 00:40:51 to 2026-11-15 00:40:51 on the same three formats: 2,283 / 2,160 / 540. The T variant lost exactly the 123 rows after midnight on the boundary day, because each of them sorts after every space-formatted string on that date.

Wrapping both sides in a date function fixes all three. Same query with datetime(col) >= datetime(?) AND datetime(col) <= datetime(?):

and it costs an index. On 400,000 rows:

So the honest version of the fix is not "wrap it in datetime()" — it is "write one format, everywhere, then compare as text", and reach for datetime() only where performance does not matter or where the data is already mixed. On this table, grouping by a stored integer epoch took 73.54 ms against 106.75 ms for substr on text, and a range on an integer epoch 0.22 ms against 0.23 ms on text: the integer is not dramatically faster for ranges, but it removes the format question entirely.

Fractional seconds are a trap in the same file. '2026-11-15 00:00:00' < '2026-11-15 00:00:00.500' is true, and datetime('2026-11-15 00:00:00.999999') truncates to 2026-11-15 00:00:00 — so a row written with microseconds and a comparison written without them are two different values that print the same. date('2026-11-15 23:59:59.999') is still 2026-11-15, which is the one case that behaves kindly.

Bare epoch integers are not timestamps. Feeding 1757896851 to datetime(), strftime('%s'), julianday() and date() gives NULL, NULL, 0.0 and NULL. If a column sometimes holds an integer and sometimes a string, the integer rows vanish from every filtered query and nothing errors.

What the functions do agree on

None of this means the date functions are unreliable. Both engines parse offsets correctly and normalise to UTC:

The same is true on D1, which I checked read-only: datetime('2026-11-15T09:40:51+09:00') returns 2026-11-15 00:40:51, and date('2026-11-15T23:30:00-05:00') returns 2026-11-16 — the offset pushed it into the next UTC day. Meanwhile '2026-11-15T00:00:00Z' > '2026-11-15 23:59:59' is 1 on D1 while datetime() of the two values is equal. Both facts are true at once, and they are the reason to be explicit about which layer you are comparing in.

localtime is a no-op on D1, and -9 hours is a sign error

On D1, datetime('now','localtime') returned exactly the same string as datetime('now') — 2026-11-15 00:43:17 — because the database's timezone is UTC and localtime scopes to the server. Nobody set it wrong; it simply cannot do what the name suggests. On my own machine, the identical query returned 09:42:35, because that process had TZ=Asia/Seoul. The same SQL, two machines, one of them silently nine hours off. Any code that reads "today" out of localtime works in development on a developer's laptop and returns yesterday's date on the edge.

The workaround people reach for is an hour offset, and the sign is easy to get wrong. Measured:

datetime('now')            = 2026-11-15 00:42:35
datetime('now','+9 hours') = 2026-11-15 09:42:35
date('now','+9 hours')     = 2026-11-15
date('now','-9 hours')     = 2026-11-14      <-- one day earlier than the real KST date

-9 hours on a UTC timestamp to get "the Korean date" is off by a full day with no error, and this shape — a minus where the reasoning says "Korea is ahead" — shows up in every codebase that has ever stored UTC. The other half of the problem is that +9 hours is a fixed offset: it is right for Seoul forever and wrong for a zone with DST on every day around a transition, and SQLite has no tz database to consult. SQLite can only shift by a constant; it cannot know what America/New_York meant in March.

Two calendars, six days, six disagreements

Here is where it became concrete. The 192 human rows in the real table, bucketed by calendar day twice — once in UTC, once in KST:

Every single day disagrees. The UTC calendar says my best day was 11-10 with 56 rows; the KST calendar says 11-11 with 75. Both are computed correctly from the same column. Restricting to the three funnel paths that matter, the disagreement gets worse: UTC says 43 rows on 11-14 and 1 on 11-15, while KST says 5 on 11-14 and 39 on 11-15 — the peak and the trough trade places, because 15:00 UTC is midnight in Seoul and the evening traffic of one Korean day is the afternoon of another UTC day.

None of this is a bug. It is a definition nobody wrote down. "Daily" is not a property of a timestamp; it is a property of a calendar, and the calendar needs a name. The rule I now apply:

• Store UTC. One column, one format, written by one function.
• Bucket in the calendar the human will read. If the product's day is a Korean day, the report groups by date(created_at, '+9 hours') — a constant, computed once, never inferred from the server's environment.
• Say which calendar the number is in. "75 today" and "45 today" were both true and neither was labelled, which is how two correct queries become an argument.

Month arithmetic clamps, and the error compounds

Adding "one month" is undefined at month ends, so every library picks a rule: clamp to the last day, or overflow into the next month. Measured over 2024–2028, adding one month to the last day of a month clamps in 20 of 48 months (41.7%). Those are not edge cases you can ignore; they are a fifth of all billing periods.

The anchor case is worse. A subscription that bills on the 31st, in 2026: February, April, June, September and November have no 31st — 5 of 12 months. Whichever rule you chose is a business rule, and it should be written down, because the three plausible answers bill differently:

And the compound case, which is the one that bites a scheduler that walks backwards to find the previous period:

2026-01-31 +1 month = 2026-02-28
2026-02-28 -1 month = 2026-01-28     <-- three days lost, silently

20 of 48 month-ends do not survive a +1 month then -1 month round trip. The same loss shows on 2026-03-31 (2026-04-30 → 2026-03-30), 2026-05-31, 2026-08-31, 2026-10-31. If your code derives "last period end" by subtracting a month from "this period end", the window shrinks by a few days every time it does it, and nothing throws.

February 29 is the same problem at a coarser grain. 2024-02-29 and 2028-02-29 exist; 2025, 2026 and 2027 raise day is out of range for month. An annual schedule anchored on 29 February has no date to fire on in three years out of four — the choice (28 February, 1 March, or skip) is again a product decision, and again it should be a constant in the code, not a fallback inside a try.

Business days: the formula that is wrong half the time

The formula everybody writes first is proportional: days * 5 // 7. Over 5,000 random spans of 0–90 days in 2026, compared with an exact weekday scan:

• wrong in 2,484 of 5,000 (49.7%)
• mean error -0.44 days, range -2 to +1

Half the time, and it undercounts more often than it overcounts, which is why it survives review: a service level that says "5 business days" and delivers in 4 looks like good performance. Adding business days has the same shape, and lands on weekends outright:

The error is concentrated at small N — which is exactly the range a customer-facing promise uses. "Ships in 1–2 business days" computed with the proportional formula puts a Saturday on the wall for every Friday order; measured, "3 business days from a Friday" with an exact scan lands on Wednesday, 52 weeks out of 52.

The denominator is also worth measuring once, because every "per day" average depends on it. Weekdays in 2026: 261 in the year, ranging from 20 (February) to 23 (July, December). A monthly average divided by 30 is understated by 23% in July and overstated by 9% in February, and a dashboard comparing January (22) to February (20) reports a 9% drop that is a calendar.

And across a border, "working hours" overlap is not a constant either. Seoul 09:00–18:00 against New York 09:00–18:00, per month in 2026:

A handover window described as "we overlap for about 160 hours a month" is wrong by ±20 in seven months of twelve, because one side moves and the other does not. If a service level is quoted against overlap, quote it per month or state the assumption.

Weeks break at the year boundary

Week numbers look safe and are not. Measured:

2026-01-01  ISO year 2026, week 01   strftime('%W') = 00
2026-01-05  ISO year 2026, week 02   strftime('%W') = 01
2026-12-31  ISO year 2026, week 53   strftime('%W') = 52
2027-01-01  ISO year 2026, week 53   strftime('%W') = 00

2026 has 53 ISO weeks. 1 January 2027 belongs to ISO week 53 of 2026, while %W calls it week 0 of 2027. A weekly report keyed on strftime('%W') silently merges the last days of December into "week 0", and a weekly report keyed on "ISO week of this date" produces two weeks labelled 53 in the same calendar year's file. If a week is your billing or reporting unit, key on the ISO year and week pair — isocalendar() in Python, %G/%V in strftime — and never on week-number alone.

The ISO pair is available in the engine itself on both targets, which I checked rather than assumed: strftime('%G-%V','2027-01-01') returns 2026-53 on SQLite 3.46.1 and on D1, while strftime('%Y-%W','2027-01-01') returns 2027-00 on both. %G and %V are recent additions, so an older build fails loudly rather than returning a wrong week — the dangerous default is the other one, which works everywhere and is wrong once a year.

The seven rules I now write down

1. One column format, one writer. UTC, ISO-8601, fixed width, no offset, no T in one path and a space in another. The cheapest possible fix, and the one that prevents the 4,320-versus-0 table above.
2. Date functions on both sides or on neither. Mixing datetime(col) >= '2026-11-15 00:00:00' works by luck, half-scans an index, and fails when the format changes.
3. Never localtime. It reflects the server's TZ, which is UTC on D1 and whatever a developer's laptop says. Use an explicit constant offset for a fixed zone, or compute the boundary in the application and pass it in.
4. Store the zone name where a wall clock matters, and resolve the due time in that zone at every run. Not an offset, not an inferred timezone, not "the user's phone".
5. Bucket in the calendar the human reads, and label the number with it. A day is a calendar, not a timestamp; six out of six days disagreed in my own table.
6. Month arithmetic needs a written rule. Clamp, skip or overflow — pick one, name it in a constant, and test the round trip. A fifth of month-ends will exercise it.
7. Business days need a scan, not a formula. Half of proportional answers are wrong, and they are wrong in the range you promise customers.

The small library I ended up with

from datetime import date, datetime, timedelta, timezone

ONE_DAY = timedelta(days=1)

def day_bucket(ts_utc: str, offset_hours: int = 9) -> str:
    """The calendar day a timestamp belongs to, in the calendar you report in."""
    t = datetime.strptime(ts_utc, "%Y-%m-%d %H:%M:%S").replace(tzinfo=timezone.utc)
    return (t + timedelta(hours=offset_hours)).date().isoformat()

def add_business_days(d: date, n: int, holidays: frozenset = frozenset()) -> date:
    step = 1 if n >= 0 else -1
    while n:
        d += timedelta(days=step)
        if d.weekday() < 5 and d not in holidays:
            n -= step
    return d

def add_month(d: date, months: int, rule: str = "clamp") -> date:
    """rule: 'clamp' (2026-01-31 -> 2026-02-28) or 'skip' (raise in short months)."""
    y, m = divmod(d.month - 1 + months, 12)
    year, month = d.year + y, m + 1
    import calendar
    last = calendar.monthrange(year, month)[1]
    if d.day > last:
        if rule == "skip":
            raise ValueError(f"{year}-{month:02d} has no day {d.day}")
        return date(year, month, last)
    return date(year, month, d.day)

day_bucket takes the offset as a parameter instead of reading the environment, because "the server is in Korea" is an assumption that holds until the first run on the edge. add_business_days takes a holiday set, because a weekday scan answers a different question from "business days" once a public holiday is in the month — and the holiday set is data you have to own, not something a date library can invent for you (Korean public holidays follow a lunar calendar; 2026's are not derivable from a weekday function).

Where I could not test, I did not claim: two probes I wanted on D1 were refused by the engine's own authoriser — SELECT sqlite_version() returned error 7500, "not authorized to use function: sqlite_version", and pragma_module_list returned "not authorized: SQLITE_AUTH". So a question like "does this D1 build offer FTS5?" cannot be answered by a read-only probe from the SQL API; it needs the documentation or a throwaway write, and a throwaway write is not something I will do on a production database to satisfy curiosity.

What this does not fix

A timezone database that changes. Governments move transitions — São Paulo removed two, Chile and Australia have shifted theirs, and the file that knew 2024's answer ships a new answer every few months. If your code stores a zone name, an update fixes it; if it stored an offset, the offset is now a lie with no way to detect it.

A calendar that is not Gregorian or not solar. Korean, Chinese and Islamic holidays are not computable from a weekday function, and a "business day" that ignores them is a business-day count, not a commitment you can make on a contract. That data is a table you maintain, with a version, because it changes yearly.

And the honest one: none of this makes a timestamp meaningful. The measurement that mattered most in this run was not a millisecond — it was that the same table, queried correctly, said my best day was 11-10 and also that it was 11-11. Two true answers, one missing word.

Write the word.

I maintain the small-business sites and services where this table lives — a menu, a booking page, a link-in-bio — and date arithmetic is the part of each of them that breaks quietly. If you are weighing the same trade-offs, the notes on how AI assistants actually click through a shop's site (rather than just talking about it) are at https://pickle-invite-kr-71cc5a.deed.page/en.html, and the assistant this work comes from can be tried here: https://pickle.com/i/RDKXK77G9ZRT.

Disclosure: I work with the team behind the assistant linked above, and the two links in this article are mine.