Recommended Free Tools
A SQLite freshness query can return a plausible result that is far too large when its timestamp strings use different formats. In an incident described by DEV Community author ushiro, comparing stored values such as 2026-08-24T17:40:41.965Z with datetime('now', '-1 hour') returned 1,252 rows; the author reported that 68 was the correct count. The cause was a text-format mismatch, not an SQL error. Those figures describe one incident, not how often the problem occurs across SQLite applications.
Why did the query count so many rows?
SQLite does not have a dedicated date/time storage type. Dates and times may be stored as text, Julian day numbers, or Unix timestamps, so the meaning of a comparison depends on the values and expression being compared. SQLite’s date and time functions documentation describes these conventions and the formats returned by its functions.
As an Amazon Associate I earn from qualifying purchases.
In the reported incident, the crawl_runs.started_at column contained UTC-style text with a T between the date and time and a trailing Z. The query compared it with datetime('now', '-1 hour'), whose result used a space separator and had no trailing Z. SQLite’s datetime() function returns text in the form YYYY-MM-DD HH:MM:SS; it does not make a differently formatted text value on the other side of an ordinary comparison behave as though both strings had been parsed into dates.
With these fixed-width strings, the first mismatch is the separator: T sorts after a space. When the date portion is the same, that can make stored values compare as greater than the cutoff even if their actual times are earlier. The query can therefore include records outside the intended rolling hour without raising an error. The outcome depends on the actual column values, types, and comparison semantics, so inspect your own data rather than assuming every SQLite timestamp query behaves this way.
#1 Best Overall
Make the cutoff match the stored representation
If the column uses ISO-style UTC text with a T separator, format the cutoff the same way. The incident’s example was:
-- Mismatched text formats
WHERE started_at > datetime('now', '-1 hour')
-- Matching T separator and UTC suffix
WHERE started_at > strftime('%Y-%m-%dT%H:%M:%SZ', 'now', '-1 hour')SQLite’s strftime() formats date/time values according to the supplied substitutions, and 'now' is interpreted in UTC. Check the documentation for the SQLite version you deploy to confirm support for the substitutions you use: SQLite date and time functions.
Rank #2
Match precision as well as punctuation
The example cutoff ends at whole-second precision, but the sample stored values include fractional seconds. If records near the boundary matter, choose and consistently apply a precision that meets the application’s needs. The key is that stored values and cutoff values must use a representation whose lexical ordering matches chronological ordering; matching only the separator is not enough if other components vary.
Free tools Windows power users keep installed
One-click scans. No signup required.
Other consistent representations
The author also describes a mechanical option: replace the space in datetime() output with T and append Z. Another option is to store timestamps numerically, such as Unix time, and compare numbers. Numeric values avoid text-separator mismatches and are straightforward to compare, but are less immediately readable during ad-hoc inspection. SQLite also supports Julian day numbers. These are design choices, not universal prescriptions; pick one representation, handle timezone and precision consistently, and document it.
| Representation | Comparison and ordering | Manual inspection | Precision and timezone considerations |
|---|---|---|---|
| Text timestamps | Chronological ordering works when values use a consistent sortable format; mismatched formats can break the intended comparison. | Readable in query results and database tools. | Choose consistent timezone notation and fractional-second precision across stored values and bounds. |
| Numeric timestamps, such as Unix time | Direct numeric comparison avoids text-format ordering mismatches. | Less self-explanatory without conversion. | Define the unit, precision, and timezone interpretation used by the application. |
The trade-offs above are practical characteristics, not a benchmark. Moving an existing column to another representation also requires a deliberate conversion and validation plan.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How to diagnose an inflated freshness count
- Inspect representative values. Select several
started_atvalues and check separators, suffixes, fractional seconds, and whether the column contains mixed formats or storage types. - Inspect both sides of the comparison. Run the cutoff expression on its own and compare its exact output with a stored value. Look for application-generated bounds and SQL-generated bounds that use different conventions.
- Check the boundary with grouped buckets. The author cross-checked hourly counts by grouping on the first 13 characters of the fixed-width UTC text format. That shortcut is appropriate only if your values actually share that format; otherwise, group using a method suited to your representation.
- Compare against a consistent cutoff. After aligning representation and precision, rerun the rolling-window query and verify that its result agrees with the bucket-level check and the expected time boundary.
For this incident, the author said the application generated bounds in JavaScript with toISOString() and was unaffected; the faulty query was hand-written operational SQL. That is the author’s account, not an independent audit of the application or a finding about other deployments. The original report, published September 11, 2026, says the issue was found and fixed on August 24, 2026: ushiro’s incident report on DEV Community.
Quick Recap
Rank #4
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
