Dates and Time Fields
Every iPCV record includes at least two date/time fields. Choosing the right one depends on whether you need to know when an event happened or when it was processed.
Event-Timing Fields
Section titled “Event-Timing Fields”These fields describe the clinical timeline. Column names may vary if you have migrated from iPCV v1.
Clinical Event Date (effective_datetime, effective_date)
Section titled “Clinical Event Date (effective_datetime, effective_date)”Records when a clinical event happened. This value is entered by the user recording the activity and is not a generated column. Historical and future dates can be entered.
The data in this column is presented as-is from the source system and some records can have dates in the distant past that are not clinically possible. Any analysis using this column will need to take this into account.
Use this column when carrying out clinical analytics, for example when calculating disease incidence and prevalence.
System Entry Date (availability_datetime, recorded_datetime, recorded_date, entered_date, entered_time)
Section titled “System Entry Date (availability_datetime, recorded_datetime, recorded_date, entered_date, entered_time)”Generated in source systems when an entry is first made. It remains static if there are later updates to the record, so recently updated records can have historical dates — this does not indicate a delay in data processing.
If an entry is not directly entered into the source system (for example, an imported record), this column may contain incongruous values. The data is presented as-is from the source system and future dates can be present. Caution and a good understanding of the data are advised if this column is used for analytics.
Processing-Timing Fields
Section titled “Processing-Timing Fields”These fields describe the data pipeline timeline and are used to manage data ingestion.
Ingestion Date (load_datetime)
Section titled “Ingestion Date (load_datetime)”When a record is ingested from source systems and loaded into the EXA datalake. This is the first step in the iPCV data processing.
Last Processed Date (transform_datetime)
Section titled “Last Processed Date (transform_datetime)”When iPCV last processed the record — that is, when it was last created, changed, or deleted from iPCV. This is the last step in the iPCV data processing and represents when the data is visible to customers.
Use this column when detecting changes in the data, processing deltas and recording up to when you last successfully loaded data.
Legacy Execution Date (_execution_date)
Section titled “Legacy Execution Date (_execution_date)”Carries the same information as transform_datetime as the equivalent
yyyyMMddHHmmss string. This exists to support customers migrating from iPCV
v1. Use transform_datetime over _execution_date for more efficient data
filtering.
Frequently Asked Questions
Section titled “Frequently Asked Questions”Why did a record appear today with an effective date from last year?
Section titled “Why did a record appear today with an effective date from last year?”Because effective_date is entered by the clinician and reflects when the event
happened, not when iPCV processed it. See
Event-Timing Fields above.
Which date field do I filter on for a clinical report?
Section titled “Which date field do I filter on for a clinical report?”effective_date or effective_datetime — you want to know when the event
occurred.
Should I use transform_datetime or execution_date for incremental sync?
Section titled “Should I use transform_datetime or execution_date for incremental sync?”Use transform_datetime, unless you are migrating an existing v1 integration
that already uses execution_date.
How do I use transform_datetime to detect data changes and process deltas?
Section titled “How do I use transform_datetime to detect data changes and process deltas?”See Understanding Deltas.