DateTime Conversion Errors and UTC Modifier Misapplication in SQLite

Undefined Behavior in UTC/Local Time Zone Conversions with Date Functions

Core Problem: Ambiguous Time Zone State Tracking in DateTime Operations

The core issue revolves around SQLite’s internal handling of time zone conversions when applying modifiers like 'utc' or 'localtime' to date/time values. Specifically, when a date/time value is already in UTC (e.g., from datetime('now') or UNIX epoch timestamps), subsequent application of the 'utc' modifier would erroneously attempt redundant time zone conversions. This stems from incomplete state tracking in the DateTime structure’s tzSet flag, which indicates whether a time value already accounts for time zone adjustments.

The DateTime structure contains critical state flags:

• validJD: Indicates valid Julian Day number
• tzSet: Marks explicit time zone setting
• validTZ: Confirms time zone offset calculation

In unpatched versions, operations like datetime('now') create DateTime objects without setting tzSet, even though 'now' inherently returns UTC time from sqlite3StmtCurrentTime(). When users then apply 'utc' modifier, the date.c code would misinterpret this as needing conversion from local time to UTC, triggering unnecessary system calls to osLocaltime() that could fail in restricted environments using SQLITE_TESTCTRL_LOCALTIME_FAULT.

Key Technical Interactions:

1. Current Time Acquisition
   setDateTimeToCurrent() initializes p->iJD with UTC time but leaves tzSet=0
   ⇒ Subsequent modifiers assume local time basis

2. Raw Date Number Parsing
   setRawDateNumber() processes UNIX epoch inputs without setting tzSet
   ⇒ UTC-origin numbers treated as local time

3. Time Zone Conversion Logic
   toLocaltime() resets tzSet during conversions, creating ambiguity
   ⇒ Multiple modifiers cause undefined behavior

Example Failure Scenario:

-- In environment with disabled localtime:
SELECT datetime('now', 'utc'); -- Pre-patch: Throws "local time unavailable"
                              -- Post-patch: Returns UTC without error

The unpatched code path:

1. 'now' → UTC time stored with tzSet=0
2. 'utc' modifier checks tzSet
   → Assumes value is in local time (due to tzSet=0)
   → Attempts conversion using osLocaltime()
   → Fails due to disabled localtime

Root Causes of UTC Modifier Misapplication and Test Failures

1. Implicit Time Zone State Mismatch in DateTime Initialization

The original implementation failed to properly mark UTC-derived timestamps as timezone-aware. When initializing DateTime objects from:

• sqlite3StmtCurrentTime() (current UTC time)
• UNIX epoch numbers ('unixepoch' modifier)
• Raw Julian Day Numbers

The tzSet flag remained unset (0), falsely indicating that the timestamp needed time zone conversion. This violated the principle that UTC-origin values should explicitly prevent redundant conversions.

2. Inconsistent State Handling During Value Transformations

The toLocaltime() function’s reset of tzSet created ambiguous state transitions:

p->tzSet = 0; // Reset during localtime conversion

When chaining modifiers like 'localtime' followed by 'utc', this reset caused the second modifier to misinterpret the time’s origin, potentially applying incorrect offset adjustments.

3. Test Suite Dependency on Legacy Error Conditions

The failing test tkt-bd484a090c.test relied on specific error throwing when using 'utc' modifier under SQLITE_TESTCTRL_LOCALTIME_FAULT:

SELECT datetime('now', 'utc'); -- Expected pre-patch error

After the patch, 'now' correctly marks the DateTime as UTC (tzSet=1), bypassing the localtime system call that would trigger the fault. The test became invalid because:

• Input time is explicitly UTC (tzSet=1)
• 'utc' modifier becomes no-op (correct behavior)
• No system call occurs ⇒ No error thrown

Comprehensive Resolution Strategy for Time Zone Handling and Test Adjustments

Phase 1: Validate DateTime State Transitions

Step 1: Audit tzSet Flag Propagation

Review all functions initializing DateTime objects:

• setDateTimeToCurrent(): Must set tzSet=1 after sqlite3StmtCurrentTime()
• setRawDateNumber(): Set tzSet=1 for UTC-based inputs (epoch, Julian)
• toLocaltime(): Reset tzSet=0 only when converting to local time

Code Correction Example:

// In setDateTimeToCurrent():
p->iJD = sqlite3StmtCurrentTime(context);
if( p->iJD>0 ){
  p->validJD = 1;
  p->tzSet = 1; // Mark as UTC-derived
  return 0;
}

// In setRawDateNumber():
p->rawS = 1;
p->tzSet = 1; // Raw numbers treated as UTC

Step 2: Implement Modifier No-Optimization

Modify date.c to skip unnecessary conversions:

if( zMod=='utc' && p->tzSet ){
  // Already UTC - no conversion needed
  continue; 
}
if( zMod=='localtime' && !p->tzSet ){
  // Already localtime - no conversion
  continue;
}

Phase 2: Update Test Suite Expectations

Adjust tkt-bd484a090c.test to Use Non-UTC Input:

-- Original failing test:
do_test 2.2 {
  catchsql { SELECT datetime('now', 'utc') }
} {1 {local time unavailable}}

-- Revised test using localtime-dependent input:
do_test 2.2 {
  catchsql { SELECT datetime('2000-01-01 00:00', 'utc') }
} {1 {local time unavailable}}

This change forces the 'utc' modifier to attempt conversion from local time (implied by string without UTC marker), triggering the expected error under SQLITE_TESTCTRL_LOCALTIME_FAULT.

Phase 3: Handle Custom Function Compatibility

For user-defined functions affected by tzSet changes (as noted in follow-up comments):

1. Time Zone Offset Formatting Functions:

// Before patch:
void format_with_offset(sqlite3_context *ctx){
  DateTime x;
  computeJD(&x); // Implicitly used localtime
  sqlite3_result_text(ctx, x.tzOffset, -1, SQLITE_TRANSIENT);
}

// Post-patch adjustment:
void format_with_offset(sqlite3_context *ctx){
  DateTime x;
  computeJD(&x);
  if( x.tzSet ){ // UTC source requires offset calculation
    sqlite3_int64 utc = x.iJD;
    toLocaltime(&x); // Convert to local for offset
    x.tzOffset = (x.iJD - utc)/86400000.0 * 1440;
  }
  // ... rest of formatting
}

2. Migration Path for Legacy Behavior:
Add SQLITE_CONFIG_LEGACY_DATETIME compile-time option to disable tzSet auto-marking, preserving pre-patch conversion behavior for compatibility.

Phase 4: Documentation and Error Code Updates

1. Clarify Modifier Behavior in Documentation:
Update https://sqlite.org/lang_datefunc.html to specify:

• 'utc' modifier is no-op on UTC-origin times
• Multiple modifiers may cancel each other
• Explicit timezone markers (+HH:MM) override modifier chain

2. Enhance Error Reporting:
Introduce new error codes:

• SQLITE_MISUSE_UTC_MODIFIER for redundant 'utc' application
• SQLITE_TZSTATE_CONFLICT for mixed modifier chains

3. Release Note Guidance:
Highlight backward-incompatible changes in time zone handling:

• Applications relying on double UTC conversion will break
• Test suites using SQLITE_TESTCTRL_LOCALTIME_FAULT require updates
• Custom date functions must check tzSet state

Final Validation Checklist

1. Conformance Tests:

   • Verify datetime('now') ≡ datetime('now', 'utc')
   • Confirm datetime(0, 'unixepoch') ≡ datetime(0, 'unixepoch', 'utc')
   • Test modifier chains: datetime('now', 'localtime', 'utc') → Original UTC time

2. Edge Case Verification:

   -- Crossing daylight saving boundaries
   SELECT datetime('2024-03-10 02:30:00', 'localtime', 'utc') 
   -- Expect proper DST-aware conversion
   
   -- Negative UNIX epochs
   SELECT datetime(-62167219200, 'unixepoch', 'utc') 
   -- Should return '0000-01-01 00:00:00'
   

3. Performance Benchmarking:

   • Measure reduction in osLocaltime() calls when using UTC modifiers
   • Profile memory usage with large-scale date conversions

4. Cross-Platform Validation:

   • Test Windows FILETIME vs UNIX epoch handling
   • Verify ICU integration compatibility
   • Check 32-bit and big-endian system support

This comprehensive approach resolves the undefined behavior in time zone conversions while maintaining backward compatibility through careful state management and clear documentation updates.