SQLite unixepoch() Truncates Milliseconds: Documentation Clarification & Behavior
Understanding the Truncation Behavior of unixepoch() with Sub-Second Precision
1. The Ambiguity in unixepoch()’s Handling of Fractional Seconds
The unixepoch() function in SQLite converts a date/time string or value into a Unix timestamp—the number of seconds since 1970-01-01 00:00:00 UTC. While the SQLite documentation explicitly states that unixepoch() returns an integer, it does not clarify whether fractional seconds (milliseconds or microseconds) in input time-values are truncated (discarded without rounding) or rounded (to the nearest whole second). This ambiguity impacts applications relying on precise time calculations, such as event logging, synchronization, or time-sensitive data aggregation.
For example, consider two timestamps: 2016-05-01T02:31:45.818Z and 2016-05-01T02:31:46.111Z. When passed to unixepoch(), both return 1462069905 and 1462069906, respectively. The first timestamp’s milliseconds (818 ms) are discarded, resulting in the same integer as if the input were 2016-05-01T02:31:45Z. Similarly, the second timestamp’s 111 ms do not round up the result to 1462069906 early; instead, the fractional part is stripped, and the integer reflects the base second.
This behavior is critical for developers who assume that unixepoch() might round to the nearest second (e.g., treating 02:31:45.999 as 02:31:46). Misunderstanding this truncation can lead to off-by-one errors in time comparisons, incorrect indexing, or mismatches with systems that use rounding.
2. Why unixepoch() Truncates Instead of Rounds: Design and Implementation Factors
The truncation behavior arises from SQLite’s internal date/time handling mechanisms and design choices. Three primary factors contribute to this:
a. Julian Day Number Conversion
SQLite internally represents dates as Julian Day Numbers (JDN), a continuous count of days since noon UTC on November 24, 4714 BC. Time values, including fractional seconds, are stored as floating-point offsets from the JDN. For example, 2016-05-01T02:31:45.818Z is stored as the JDN for 2016-05-01 plus (2*3600 + 31*60 + 45.818)/86400 days.
When unixepoch() calculates the Unix timestamp, it first converts the input to a JDN (with fractional days), then subtracts the JDN for the Unix epoch (2440587.5) to get the number of days since 1970-01-01. This value is multiplied by 86400 to convert days to seconds. However, the fractional part of the day is also converted to seconds using floating-point arithmetic. For instance:
0.5 days = 43200 seconds0.123456 days ≈ 10666.5984 seconds
The final result is cast to an integer, which effectively truncates any fractional seconds. This is why unixepoch("2016-05-01T02:31:45.818Z") returns 1462069905 instead of 1462069905.818 or a rounded value.
b. Compiler and Platform Consistency
SQLite aims for deterministic behavior across platforms. If unixepoch() relied on rounding, differences in floating-point precision or rounding modes between compilers could introduce inconsistencies. For example, a value like 1462069905.999999 might round to 1462069906 on one system but remain 1462069905 on another. Truncation ensures that the same input always yields the same integer output, regardless of the environment.
c. Scope of Date/Time Functions
SQLite’s date/time functions are designed for simplicity and broad utility rather than high-precision use cases. Sub-second precision is supported in input formats (e.g., YYYY-MM-DD HH:MM:SS.SSS), but the built-in functions prioritize whole-second resolution for Unix timestamp conversions. Applications requiring millisecond or microsecond precision are expected to handle fractional parts separately (e.g., by storing them in another column or using custom functions).
3. Validating Truncation, Adjusting Queries, and Mitigating Edge Cases
Step 1: Confirm Truncation Behavior
To verify how unixepoch() handles fractional seconds, execute test queries with known values:
SELECT unixepoch('2016-05-01T02:31:45.818Z'); -- Returns 1462069905
SELECT unixepoch('2016-05-01T02:31:45.999Z'); -- Returns 1462069905
SELECT unixepoch('2016-05-01T02:31:46.000Z'); -- Returns 1462069906
Observe that all fractional parts are discarded, confirming truncation.
Step 2: Use strftime() for Custom Formatting
If rounding is required, combine strftime() with arithmetic operations:
SELECT CAST(strftime('%s', '2016-05-01T02:31:45.818Z') AS INTEGER) +
ROUND((julianday('2016-05-01T02:31:45.818Z') - julianday(strftime('%Y-%m-%d %H:%M:%S', '2016-05-01T02:31:45.818Z'))) * 86400);
This calculates the Unix timestamp of the whole-second part and adds the rounded fractional seconds.
Step 3: Store Fractional Seconds Separately
For applications requiring millisecond precision, split the timestamp into two columns:
CREATE TABLE events (
id INTEGER PRIMARY KEY,
event_time TEXT, -- ISO8601 string with milliseconds
unix_seconds INTEGER GENERATED ALWAYS AS (unixepoch(event_time)),
milliseconds INTEGER GENERATED ALWAYS AS (CAST(strftime('%f', event_time) * 1000 AS INTEGER) % 1000)
);
This approach retains both the whole-second Unix timestamp and the fractional part.
Step 4: Handle Edge Cases Near Second Boundaries
When filtering or grouping by time, explicitly account for truncation. For example, to retrieve events between 2023-01-01T00:00:00.500Z and 2023-01-01T00:00:01.500Z:
SELECT * FROM events
WHERE unixepoch(event_time) >= unixepoch('2023-01-01T00:00:00Z')
AND unixepoch(event_time) <= unixepoch('2023-01-01T00:00:01Z');
This ensures that all events with fractional seconds in the range [00:00:00.000, 00:00:01.999] are included.
Step 5: Update Documentation References
Refer to the revised SQLite documentation, which now clarifies that unixepoch() truncates fractional seconds. For dates after 1970, this behavior is consistent across platforms. For ancient dates (pre-4714 BC), consult specialized time systems, as SQLite’s date functions are not optimized for astronomical calculations.
Step 6: Use Application-Level Rounding
If rounding is unavoidable, pre-process timestamps before passing them to unixepoch():
SELECT unixepoch(strftime('%Y-%m-%d %H:%M:%S', '2016-05-01T02:31:45.818Z', '+0.5 seconds'));
This rounds to the nearest second by adding half a second before truncation.
By understanding the truncation mechanism, leveraging SQLite’s date functions judiciously, and structuring schemas to isolate fractional seconds, developers can avoid common pitfalls and ensure accurate time handling in their applications.
