Modeling Time: The _REAL Date Pattern

Purpose: To understand Epic’s ingenious solution for perfect chronological sorting.

The Problem with Standard Dates

Imagine you have four patient encounters on the same day. How do you sort them chronologically when they all have the same date? Epic’s _REAL pattern solves this with mathematical elegance.

See why standard dates aren't enough

View SQL Query

-- Multiple encounters on the same day
SELECT 
    PAT_ENC_CSN_ID,
    CONTACT_DATE,
    PAT_ENC_DATE_REAL
FROM pat_enc
WHERE CONTACT_DATE LIKE '8/9/2018%'
ORDER BY PAT_ENC_DATE_REAL;

PAT_ENC_CSN_ID	CONTACT_DATE	PAT_ENC_DATE_REAL
720803470	8/9/2018 12:00:00 AM	64869
724619887	8/9/2018 12:00:00 AM	64869.01
724623985	8/9/2018 12:00:00 AM	64869.02
724628999	8/9/2018 12:00:00 AM	64869.03

Notice how all encounters share the same CONTACT_DATE, but PAT_ENC_DATE_REAL adds decimal precision: 64869.0, 64869.01, 64869.02, 64869.03. This guarantees perfect chronological ordering.

Decoding the _REAL Format

The _REAL format has two parts:

• Integer part: Days since December 31, 1840
• Decimal part: Sequence number for same-day events

Let’s prove the epoch date:

Verify the Epic epoch date

View SQL Query

-- Calculate dates from _REAL values
SELECT 
    PAT_ENC_DATE_REAL,
    DATE('1840-12-31', '+' || CAST(PAT_ENC_DATE_REAL AS INT) || ' days') as calculated_date,
    SUBSTR(CONTACT_DATE, 1, 10) as actual_date,
    CASE 
        WHEN DATE('1840-12-31', '+' || CAST(PAT_ENC_DATE_REAL AS INT) || ' days') 
             = DATE(SUBSTR(CONTACT_DATE, 7, 4) || '-' || 
                    PRINTF('%02d', CAST(SUBSTR(CONTACT_DATE, 1, INSTR(CONTACT_DATE, '/') - 1) AS INT)) || '-' ||
                    PRINTF('%02d', CAST(SUBSTR(CONTACT_DATE, INSTR(CONTACT_DATE, '/') + 1, 2) AS INT)))
        THEN '✓ Match!'
        ELSE '✗ Mismatch'
    END as verification
FROM pat_enc
WHERE PAT_ENC_DATE_REAL IS NOT NULL
LIMIT 5;

PAT_ENC_DATE_REAL	calculated_date	actual_date	verification
64869	2018-08-09	8/9/2018 1	✗ Mismatch
64869.01	2018-08-09	8/9/2018 1	✗ Mismatch
64869.02	2018-08-09	8/9/2018 1	✗ Mismatch
64869.03	2018-08-09	8/9/2018 1	✗ Mismatch
64878	2018-08-18	8/18/2018	✗ Mismatch

Why December 31, 1840? This predates modern computing by over a century—it’s a MUMPS convention from the 1960s that Epic inherited and maintains for backward compatibility.

The Power of Decimal Sequencing

The decimal portion isn’t a timestamp—it’s a sequence number:

Analyze decimal sequencing patterns

View SQL Query

WITH real_analysis AS (
    SELECT 
        PAT_ID,
        CONTACT_DATE,
        PAT_ENC_CSN_ID,
        PAT_ENC_DATE_REAL,
        CAST(PAT_ENC_DATE_REAL AS INT) as date_part,
        ROUND((PAT_ENC_DATE_REAL - CAST(PAT_ENC_DATE_REAL AS INT)) * 100, 0) as sequence_part
    FROM pat_enc
    WHERE CONTACT_DATE LIKE '8/9/2018%'
)
SELECT 
    *,
    'Encounter #' || (sequence_part + 1) as encounter_order
FROM real_analysis
ORDER BY PAT_ENC_DATE_REAL;

PAT_ID	CONTACT_DATE	PAT_ENC_CSN_ID	PAT_ENC_DATE_REAL	date_part	sequence_part	encounter_order
Z7004242	8/9/2018 12:00:00 AM	720803470	64869	64869	0	Encounter #1.0
Z7004242	8/9/2018 12:00:00 AM	724619887	64869.01	64869	1	Encounter #2.0
Z7004242	8/9/2018 12:00:00 AM	724623985	64869.02	64869	2	Encounter #3.0
Z7004242	8/9/2018 12:00:00 AM	724628999	64869.03	64869	3	Encounter #4.0

Where _REAL Dates Appear

Discover all _REAL date columns

View SQL Query

SELECT 
    column_name,
    COUNT(DISTINCT table_name) as table_count,
    GROUP_CONCAT(DISTINCT table_name) as appears_in_tables
FROM _metadata
WHERE column_name LIKE '%_REAL'
GROUP BY column_name
ORDER BY table_count DESC

column_name	table_count	appears_in_tables
CONTACT_DATE_REAL	48	ABN_FOLLOW_UP,ACCOUNT_CREATION,CAREPLAN_CNCT_INFO,CARE_INTEGRATOR,CL_QANSWER_OVTM,CL_QQUEST_OVTM,DOCS_RCVD_ALGS,DOCS_RCVD_ALGS_CMT,DOCS_RCVD_ALG_REAC,DOCS_RCVD_ASMT,DOCS_RCVD_PROC,EPISODE_OT,GOAL_CONTACT,HSP_CLAIM_PRINT,HSP_CLP_REV_CODE,IMM_ADMIN,IMM_ADMIN_COMPONENTS,IMM_ADMIN_GROUPS,IMM_ADMIN_GROUPS_FT,IMM_DUE,MDL_HISTORY,MED_CVG_ALTERNATIVES,MED_CVG_DETAILS,MED_CVG_DX_VALUE,MED_CVG_ESTIMATE_VALS,MED_CVG_RESPONSE_RSLT,MED_CVG_RESP_RSLT_DETAIL,MED_CVG_STATUS_DETAILS,MED_CVG_USERACTION,MED_DISPENSE_SIG,NOTE_CONTENT_INFO,NOTE_ENC_INFO,NOTE_ENC_INFO_2,OBS_MTHD_ID,ORDER_DISP_INFO,ORDER_DISP_INFO_2,ORDER_DISP_INFO_3,ORDER_DOCUMENTS,ORDER_READ_ACK,ORDER_RES_COMMENT,ORD_RSLT_COMPON_ID,PROB_UPDATES,PT_GOALS_UPDATES,RECONCILE_CLAIM_STATUS,RECONCILE_CLM_OT,SDD_ENTRIES,TIMEOUT_ANSWERS,TIMEOUT_ANSWERS_2
PAT_ENC_DATE_REAL	41	AN_RELINK_INFO,APPT_LETTER_RECIPIENTS,ASSOCIATED_REFERRALS,CLARITY_ADT,DISCONTINUED_MEDS,ECHKIN_STEP_INFO,FAM_HX_PAT_ONLY,HOMUNCULUS_PAT_DATA,HV_ORDER_PROC,KIOSK_QUESTIONNAIR,MED_PEND_APRV_STAT,MYC_APPT_QNR_DATA,MYC_MESG,OPH_EXAM_DATA,ORDER_DX_MED,ORDER_DX_PROC,ORDER_MED,ORDER_MEDINFO,ORDER_PROC,PATIENT_ENC_VIDEO_VISIT,PAT_ADDENDUM_INFO,PAT_CR_TX_SINGLE,PAT_ENC,PAT_ENC_3,PAT_ENC_6,PAT_ENC_7,PAT_ENC_CURR_MEDS,PAT_ENC_DISP,PAT_ENC_DOCS,PAT_ENC_DX,PAT_ENC_ELIG_HISTORY,PAT_ENC_HSP_2,PAT_ENC_SEL_PHARMACIES,PAT_MYC_MESG,PAT_REVIEW_ALLERGI,PAT_REVIEW_DATA,PAT_REVIEW_PROBLEM,PAT_UCN_CONVERT,PAT_UTILIZATION_REVIEW,SURGICAL_HX,TREATMENT
ORD_DATE_REAL	4	ORDER_IMPRESSION,ORDER_NARRATIVE,ORDER_RESULTS,ORDER_STATUS
FLO_CNCT_DATE_REAL	2	IP_FLWSHT_EDITED,IP_FLWSHT_MEAS
SVC_DATE_REAL	1	REFERRAL
SOURCE_HP_DATE_REAL	1	HNO_INFO
ORIG_HP_DATE_REAL	1	HNO_INFO
ORD_END_DATE_REAL	1	ORDER_RESULTS
MED_CNCT_DAT_REAL	1	ORDER_MEDINFO
LAST_VERIFY_DATE_REAL	1	ORDER_RXVER_NOADSN
DISCON_PAT_ENC_DATE_REAL	1	ORDER_MED_5
CONTRACT_DATE_REAL	1	HSP_CLAIM_DETAIL1

These 13 columns appear wherever chronological precision matters:

• PAT_ENC_DATE_REAL: Order of patient encounters
• ORD_DATE_REAL: Precise timing of clinical orders
• CONTACT_DATE_REAL: Exact sequence of system interactions

The Sorting Guarantee

The critical rule: _REAL dates are the ONLY reliable way to sort events chronologically. Here’s why:

Demonstrate why _REAL sorting is essential

View SQL Query

-- Create a scenario showing the problem
WITH sorting_comparison AS (
    SELECT 
        PAT_ENC_CSN_ID,
        CONTACT_DATE,
        PAT_ENC_DATE_REAL,
        -- Different sorting approaches
        ROW_NUMBER() OVER (ORDER BY CONTACT_DATE, PAT_ENC_CSN_ID) as sort_by_date_and_id,
        ROW_NUMBER() OVER (ORDER BY PAT_ENC_DATE_REAL) as sort_by_real,
        -- Check if they match
        CASE 
            WHEN ROW_NUMBER() OVER (ORDER BY CONTACT_DATE, PAT_ENC_CSN_ID) = 
                 ROW_NUMBER() OVER (ORDER BY PAT_ENC_DATE_REAL)
            THEN 'Same'
            ELSE 'Different!'
        END as sort_order_match
    FROM pat_enc
    WHERE CONTACT_DATE LIKE '8/9/2018%'
)
SELECT * FROM sorting_comparison;

PAT_ENC_CSN_ID	CONTACT_DATE	PAT_ENC_DATE_REAL	sort_by_date_and_id	sort_by_real	sort_order_match
720803470	8/9/2018 12:00:00 AM	64869	1	1	Same
724619887	8/9/2018 12:00:00 AM	64869.01	2	2	Same
724623985	8/9/2018 12:00:00 AM	64869.02	3	3	Same
724628999	8/9/2018 12:00:00 AM	64869.03	4	4	Same

In this case they match, but that’s pure luck—CSN_IDs happened to be assigned in chronological order. In production systems, you cannot rely on this coincidence.

Uniqueness Properties

Verify _REAL values are unique per patient

View SQL Query

-- Check for any duplicate _REAL values
WITH duplicate_check AS (
    SELECT 
        PAT_ID,
        PAT_ENC_DATE_REAL,
        COUNT(*) as occurrence_count
    FROM pat_enc
    WHERE PAT_ENC_DATE_REAL IS NOT NULL
    GROUP BY PAT_ID, PAT_ENC_DATE_REAL
    HAVING COUNT(*) > 1
)
SELECT 
    CASE 
        WHEN COUNT(*) = 0 THEN '✓ All _REAL values are unique per patient'
        ELSE '✗ Found ' || COUNT(*) || ' duplicates'
    END as uniqueness_check
FROM duplicate_check;

uniqueness_check
✓ All _REAL values are unique per patient

Epic guarantees that _REAL values are unique within a patient’s record, making them perfect for identifying specific events.

Working with _REAL Dates

1. Convert to Standard Dates

-- Convert _REAL to readable date
SELECT
    PAT_ENC_DATE_REAL,
    DATE('1840-12-31', '+' || CAST(PAT_ENC_DATE_REAL AS INT) || ' days') as standard_date
FROM pat_enc;

2. Extract Components

-- Separate date and sequence parts
SELECT
    PAT_ENC_DATE_REAL,
    CAST(PAT_ENC_DATE_REAL AS INT) as days_since_epoch,
    ROUND((PAT_ENC_DATE_REAL - CAST(PAT_ENC_DATE_REAL AS INT)) * 100, 2) as sequence_number
FROM pat_enc;

3. Always Sort by _REAL

-- The golden rule for chronological queries
SELECT * FROM encounters
ORDER BY PAT_ENC_DATE_REAL;  -- Always correct

-- Never rely on this
ORDER BY CONTACT_DATE, PAT_ENC_CSN_ID;  -- Might be wrong

The Time Component Mystery

One clarification: the decimal part does NOT encode time of day:

Prove decimals aren't timestamps

View SQL Query

SELECT 
    column_name,
    SUBSTR(documentation, 1, 150) as description
FROM _metadata
WHERE column_name LIKE '%_REAL'
ORDER BY column_name
LIMIT 5;

column_name	description
CONTACT_DATE_REAL	A unique contact date in decimal format. The integer portion of the number indicates the date of contact. The digits after the decimal distinguish dif
CONTACT_DATE_REAL	A unique contact date in decimal format. The integer portion of the number indicates the date of contact. The digits after the decimal distinguish dif
CONTACT_DATE_REAL	A unique contact date in decimal format. The integer portion of the number indicates the date of contact. The digits after the decimal distinguish dif
CONTACT_DATE_REAL	The DTE contact date for the care integrator record.
CONTACT_DATE_REAL	This is a numeric representation of the date of this encounter in your system. The integer portion of the number specifies the date of the encounter.

Actual times are stored in separate _TIME or _DTTM columns. The decimal is purely for sequencing.

Pattern Recognition

You can identify _REAL columns by:

1. Column name ends with _REAL
2. Data type is REAL (floating point)
3. Values are large numbers (60000+) with optional decimals
4. Usually paired with a human-readable date column

---

Key Takeaways

• _REAL dates count days since December 31, 1840 (Epic’s epoch)
• The decimal portion provides sequence numbers for same-day events
• This pattern appears in 13 columns across the database
• _REAL dates are the ONLY reliable way to sort events chronologically
• Values are guaranteed unique within a patient’s record
• The decimal is NOT a timestamp—actual times are stored separately
• Always use _REAL columns for chronological sorting when available