iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A SQLite query for rows from the last hour can return far too many results when stored timestamps and the cutoff use different text formats. In one incident, a query returned 1,252 rows; the author reported that only 68 were correct. The mismatch was a visible but easy-to-miss difference: stored timestamps used a T between the date and time, while SQLite’s datetime() cutoff used a space.
How the query counted 1,252 rows instead of 68
In a September 11, 2026, DEV Community post, author ushiro described a freshness check against a SQLite crawl_runs.started_at column. Values looked like 2026-08-24T17:40:41.965Z, but the query compared them with datetime('now', '-1 hour'). The cutoff in the example was 2026-08-24 16:54:52. The author reported 1,252 results from the mismatched query and 68 as the correct count for that incident. These are the author’s incident-specific figures, not an independently reproduced test or a measure of how often the problem occurs. Read the incident account on DEV Community.
SQLite does not have a dedicated date/time storage type. Applications commonly store date/time values as text, Julian day numbers, or Unix timestamps. SQLite’s datetime() function returns text with a space between the date and time; the sample stored strings instead used T and ended in Z. SQLite’s date and time functions documentation describes the function output and supported time-value conventions.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Why a formatting mismatch changes a text comparison
If the comparison is between text values, SQLite does not automatically turn both strings into dates just because they resemble timestamps. In the example, the strings share the same date, but at the separator position the stored T sorts after the cutoff’s space. As a result, same-day stored values can compare as later than the cutoff even when their clock times are earlier. That can inflate a rolling-window result without producing an SQL error.
#1 Best Overall
The exact result depends on the actual values, storage classes, and comparison semantics. Check your own column and query rather than assuming every timestamp-like value is stored or compared the same way.
Make the cutoff match the stored timestamps
For the example’s fixed-width UTC text representation, the direct correction is to format the cutoff with the same separator and UTC suffix:
-- 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() can format a date/time value using a requested format. Its now value is UTC. Consult the date/time documentation and verify that the deployed SQLite version supports the format substitutions you use.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteThe shown cutoff has whole-second precision, while the sample stored value includes fractional seconds. Matching the separator alone may not be sufficient if fractional-second precision affects which rows fall inside your window. Choose a consistent representation and precision for stored values and generated bounds.
The incident author also described a mechanical alternative: replace the space in the datetime() output with T and append Z. That can align the text shape, but it still requires deliberate handling of precision and timezone meaning. Whichever method you use, document the chosen representation so hand-written queries follow the same rules.
Choose a timestamp representation deliberately
Text and numeric Unix timestamps can both work; the important point is using a consistent representation for storage and comparison. SQLite recognizes text, Julian day numbers, and Unix timestamps as common date/time conventions, but these are conventions rather than a dedicated date/time storage type. SQLite’s datatype documentation explains its storage classes.
| Choice | Comparison and ordering | Manual inspection | Precision and timezone | Migration or conversion |
|---|---|---|---|---|
| Text timestamp | Text ordering works for consistently formatted, fixed-width timestamps representing the same timezone convention. Mixed formats can break the intended chronological comparison. | Readable in query results and database tools. | Choose a consistent precision and timezone notation; the sample uses fractional seconds and a UTC suffix. | Changing an existing column may require converting old values and updating every writer and query. |
| Numeric Unix timestamp | Numeric values can be compared directly as numbers when stored and queried in the same unit. | Less immediately readable without conversion. | Choose and document the unit and conversion rules; do not mix units. | Existing text values must be converted consistently, and application code must use the chosen numeric convention. |
The incident author favored readable text for a table that people inspect and suggested numeric storage for data used only in comparisons. That is a personal design preference, not a measured performance or reliability finding. Select based on how your application reads and writes timestamps, and keep the conversion policy explicit.
Verify a rolling-window result before trusting it
- Inspect stored values. Select a small sample from the timestamp column and check separators, suffixes, fractional seconds, and whether values are actually stored as text or numbers.
- Inspect every source of bounds. Look for SQL-generated cutoffs as well as application-generated values. The incident author reported that the application created bounds in JavaScript with
toISOString()and was unaffected; the faulty query was hand-written operational SQL. That account does not establish that other applications are protected from the same mismatch. - Compare results against time buckets. For the incident’s fixed-width UTC strings, grouping by the first 13 characters provided an hourly cross-check. For example:
SELECT substr(started_at, 1, 13) AS hour, COUNT(*) FROM crawl_runs GROUP BY hour ORDER BY hour;Adapt the grouping expression to your timestamp format; a character slice is not a general date parser. - Test boundary cases. Include rows just before and after the cutoff, including any fractional-second values your system stores. Confirm that the query includes and excludes the expected rows.
For additional context on the incident and its diagnostic approach, see ushiro’s DEV Community post. The reported August 24, 2026, fix and claim that production application code was unaffected come from that author’s account; they were not independently validated.
Quick Recap
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.
