What date and time formats are used across OpenSafely models?

OpenSafely models use SQL date/time types and are queryable as standard Trino temporal values.

General conventions

• Fields ending _date are date values with no time component.
• Fields ending _datetime are timestamp values and include a time component.
• Timestamp fields are stored as UTC values unless explicitly documented otherwise.
• Some timestamp fields are intentionally truncated to month-level precision as part of pseudonymisation and disclosure control.
• In analytical queries, treat NULL temporal values as missing data rather than a formatting error.

Common fields you will see

Field pattern	Typical meaning
effective_datetime	Clinical or business event time
model_updated_datetime	Last model processing/update time
transform_datetime	Last transform visibility update time
date_of_birth	Patient birth date
review_date	Planned review date

Query tips

• Use explicit date windows when building cohorts.
• Cast only when needed for grouping or reporting.
• Keep filtering consistent across joined models to avoid accidental skew.

SELECT
  DATE_TRUNC('month', effective_datetime) AS event_month,
  COUNT(*) AS issue_count
FROM explorer_open_safely.medication_issue_record
WHERE effective_datetime >= DATE '2026-01-01'
GROUP BY 1
ORDER BY 1

If you need model-specific field definitions, check each model’s schema.md page in the OpenSafely explorer section.