Precision Loss When Converting Datetime to Julian Day and Back in SQLite

Understanding Julian Day Conversion Precision Discrepancies

Issue Overview

The core issue revolves around unexpected precision loss when converting datetime values to Julian Day numbers (using julianday()) and back to datetime strings (using strftime()). Specifically, when a datetime string with millisecond precision (e.g., 2022-05-17 13:56:12.569Z) is parsed into a Julian Day value and then converted back to a datetime string, the resulting millisecond component is off by 1 millisecond (e.g., 568Z instead of 569Z).

This behavior is counterintuitive because the Julian Day format is designed to represent datetime values with high precision. The problem manifests when using SQLite’s built-in datetime functions for bidirectional conversions:

1. julianday('2022-05-17 13:56:12.569Z') returns 2459717.08070102.
2. Converting this Julian Day value back with strftime('%Y-%m-%d %H:%M:%fZ', 2459717.08070102) yields 2022-05-17 13:56:12.568Z—a loss of 1 millisecond.

The discrepancy arises only when the datetime value is stored or manipulated as a Julian Day number. Storing the datetime as a text string and using strftime() directly does not exhibit this issue, confirming that the problem lies in the intermediate Julian Day conversion.

Key Observations

• The error is consistent across datetime values with fractional seconds.
• The Julian Day value itself is computed with sufficient theoretical precision to represent milliseconds accurately.
• Direct string-to-string conversions avoid the error, indicating that the issue is isolated to the Julian Day conversion logic.

Root Causes: Floating-Point Precision and Rounding Errors

1. IEEE 754 Double-Precision Floating-Point Limitations
SQLite stores Julian Day numbers as 64-bit IEEE 754 floating-point values. While these values theoretically provide ~15 decimal digits of precision, their binary nature introduces rounding errors during conversions between decimal and binary formats.

• Milliseconds in Julian Day:
  A single millisecond corresponds to a fractional value of 1 / 86400000 ≈ 1.1574074074074073e-08 in the Julian Day system. Representing this fraction in binary floating-point introduces rounding errors due to the inability to precisely represent decimal fractions in binary.

• Error Accumulation:
  The error compounds when multiple operations (e.g., parsing datetime components, adding offsets) are performed during conversion. For example, truncating instead of rounding intermediate values exacerbates precision loss.

2. SQLite’s Date/Time Function Implementation
The SQLite source code (date.c) computes Julian Day values by breaking down datetime components (year, month, day, hour, minute, second) into an integer representing the number of milliseconds since the Julian epoch. A critical flaw was identified in how fractional seconds are handled:

// Original code snippet from computeJD() in date.c  
p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000);  

Here, p->s (seconds with fractional parts) is multiplied by 1000 to convert to milliseconds. The cast to sqlite3_int64 truncates the result instead of rounding it. For example:

• 12.569 seconds * 1000 = 12569.0 → No error.
• 12.56899999999999999 seconds * 1000 ≈ 12568.999999999999 → Truncated to 12568.

This truncation caused a systematic undercounting of milliseconds.

3. Guard Bit Insufficiency
The Julian Day system allocates most bits to the integer part (days since epoch), leaving fewer bits for fractional day precision. Modified Julian Day (MJD) or Reduced Julian Day (RJD) systems reduce the integer part to allocate more bits to fractional seconds, but SQLite does not natively support these formats.

Resolving Precision Loss: Workarounds, Fixes, and Best Practices

1. Apply the Official SQLite Patch
The SQLite team addressed the truncation issue in commit e5e9311863544ef3:

// Updated code with proper rounding  
p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000 + 0.5);  

Adding 0.5 before truncation ensures that fractional milliseconds are rounded to the nearest integer.

Verification Post-Fix

SELECT strftime('%Y-%m-%d %H:%M:%fZ', julianday('2022-05-17 13:56:12.569Z'));  
-- Output: 2022-05-17 13:56:12.569Z  

2. Workarounds for Unpatched SQLite Versions

• Force Rounding Manually:
  Add a small offset to the Julian Day value before conversion:

  SELECT strftime('%Y-%m-%d %H:%M:%fZ', julianday(datetime) + 1e-8);  
  

  The offset 1e-8 corresponds to ~0.864 milliseconds, compensating for truncation.

• Avoid Julian Day for Sub-Second Precision:
  Store datetimes as ISO 8601 strings or Unix epoch integers with millisecond precision:

  -- Store as text  
  INSERT INTO events (timestamp) VALUES ('2022-05-17 13:56:12.569Z');  
  
  -- Store as Unix epoch milliseconds  
  INSERT INTO events (timestamp) VALUES (1652802972569);  
  

• Use Integer Arithmetic for Computations:
  Convert datetimes to Unix epoch milliseconds (an integer) for arithmetic operations:

  SELECT (strftime('%s', '2022-05-17 13:56:12.569Z') * 1000 +  
          CAST(SUBSTR(strftime('%f', '2022-05-17 13:56:12.569Z'), 4) AS INTEGER))  
         AS unix_ms;  
  

3. Precision Validation Techniques

• Epsilon Analysis:
  Calculate the maximum error margin (epsilon) for a Julian Day value:

  SELECT 2.220446049250313e-16 * julianday('now') * 86400 AS max_error_seconds;  
  

  This estimates the worst-case precision loss in seconds.

• Round-Trip Testing:
  Verify conversions for critical datetimes:

  WITH samples(dt) AS (  
    VALUES  
    ('2022-05-17 13:56:12.569Z'),  
    ('1970-01-01 00:00:00.000Z'),  
    ('9999-12-31 23:59:59.999Z')  
  )  
  SELECT dt AS original,  
         strftime('%Y-%m-%d %H:%M:%fZ', julianday(dt)) AS converted  
  FROM samples;  
  

4. Advanced Use Cases: Modified Julian Day (MJD)
For applications requiring higher precision (e.g., astronomy), implement MJD by adjusting the epoch:

-- Convert datetime to MJD (JD - 2400000.5)  
SELECT julianday('2022-05-17 13:56:12.569Z') - 2400000.5 AS mjd;  

-- Convert MJD back to datetime  
SELECT strftime('%Y-%m-%d %H:%M:%fZ', mjd + 2400000.5) FROM events;  

This redistributes floating-point bits to improve fractional second precision.

5. Long-Term Best Practices

• Prefer Text Storage: Use ISO 8601 strings unless arithmetic operations are required.
• Monitor SQLite Updates: Ensure your SQLite version includes the rounding fix.
• Benchmark Precision Requirements: Validate that your chosen storage method meets application-specific precision needs.

By addressing both the technical limitations of floating-point arithmetic and the specific implementation flaw in SQLite, this guide provides a comprehensive roadmap for resolving datetime precision issues in SQLite-based applications.