Ocotillo should enforce UTC

[kbighorse]

see #205 (review)

[kbighorse]

Partial Implementation Status

This issue has been partially addressed by the following PRs:

PR #65 - DateTime timezone awareness (merged Aug 2025)

• All DateTime columns set to DateTime(timezone=True)
• All TIMESTAMP columns set to TIMESTAMP(timezone=True)
• Server defaults use func.timezone('UTC', func.now())
• Pydantic schemas use AwareDatetime instead of datetime.datetime
• User-defined datetime fields converted to UTC if not already

PR #205 - Additional timezone fixes (merged Oct 2025)

• Addressed timezone issues in test suite

Current State (verified Jan 2026)

Implemented UTC conversion validators:

• schemas/observation.py: convert_observation_datetime_to_utc()
• schemas/sample.py: convert_sample_date_to_utc()
• schemas/__init__.py: Base UTC conversion for AwareDatetime fields

Remaining work (from TODO in code):

# schemas/__init__.py line 112
# TODO: write function to convert any datetime field to UTC for use throughout

The sensor model datetime fields may still need UTC enforcement (noted in PR #65).

---

Recommendation: This issue could be closed if a final audit confirms all datetime fields are covered, or kept open for the remaining TODO work.

[kbighorse]

This issue has been resolved through multiple PRs.

Implementation Summary

PR #65 (merged Aug 2025) - Primary implementation

• All DateTime columns set to DateTime(timezone=True)
• All TIMESTAMP columns set to TIMESTAMP(timezone=True)
• Server defaults use func.timezone('UTC', func.now())
• Pydantic schemas use AwareDatetime instead of datetime.datetime
• UTC conversion validators added

PR #205 (merged Oct 2025) - Additional fixes

• Addressed remaining timezone issues in tests

Verification (Jan 2026)

✓ 13 DateTime columns with timezone=True across 6 model files
✓ 26 AwareDatetime usages across 6 schema files
✓ UTC conversion validators in observation.py, sample.py, __init__.py
✓ No DateTime columns found without timezone=True

Remaining Cleanup (not blocking)

Minor TODO comments remain for refactoring but core UTC enforcement is complete:

• schemas/__init__.py:112 - Helper function consolidation
• db/location.py:111 - Documentation note

The original requirement from the PR review ('store all datetime records in the database as UTC') is satisfied.