Time types

Time in the database is stored with one of two suffixes: TIME and DATE. If a column has TIME as the suffix, e.g. CHARTTIME, then the data resolution is down to the minute. If the column has DATE as the suffix, e.g. CHARTDATE, then the data resolution is down to the day. That means that measurements in a CHARTDATE column will always have 00:00:00 has the hour, minute, and second values. This does not mean it was recorded at midnight: it indicates that we do not have the exact time, only the date.

Date shifting

All dates in the database have been shifted to protect patient confidentiality. Dates will be internally consistent for the same patient, but randomly distributed in the future. This means that if measurement A is made at 2150-01-01 14:00:00, and measurement B is made at 2150-01-01 15:00:00, then measurement B was made 1 hour after measurement A.

The date shifting preserved the following:

• Time of day - a measurement made at 15:00:00 was actually made at 15:00:00 local standard time.
• Day of the week - a measurement made on a Sunday will appear on a Sunday in the future.
• Seasonality - a measurement made during the winter months will appear during a winter month.

The date shifting removed the following:

• Year - The year is randomly distributed between 2100 - 2200.
• Day of the month - The absolute day of the month is not preserved.
• Inter-patient information - Two patients in the ICU on 2150-01-01 were not in the ICU at the same time.

Dates of birth

Dates of birth which occur in the present time are not true dates of birth. Furthermore, dates of birth which occur before the year 1900 occur if the patient is older than 89. In these cases, the patient’s age at their first admission has been fixed to 300.

Time columns in the database

CHARTTIME vs STORETIME

Most data, with the exception of patient related demographics, are recorded with a time indicating when the observation was made: CHARTTIME. CHARTTIME dates back to the use of paper charts: in order to facilitate efficient observations by nursing staff, the day was separated into hourly blocks, and observations were recorded within these hourly blocks. Thus, any time one performed a measurement between the hours of 04:00 and 05:00, the data would be charted in the 04:00 block, and so on. This concept has carried forward into the electronic recording of data: even if data is recorded at 04:23, in many cases it is still charted as occurring at 04:00.

STORETIME provides information on the recording of the data element itself. All observations in the database must be validated before they are archived into the patient medical record. The STORETIME provides the exact time that this validation occurred. For example, a heart rate may be charted at 04:00, but only validated at 04:40. This indicates that the care provider validated the measurement at 4:40 and indicated that it was a valid observation of the patient at 04:00. Conversely, it’s also possible that the STORETIME occurs before the CHARTTIME. While a Glasgow Coma Scale may be charted at a CHARTTIME of 04:00, the observation may have been made and validated slightly before (e.g. 3:50). Again, the validation implies that the care staff believed the measurement to be an accurate reflection of the patient status at the given CHARTTIME.

Summing up: CHARTTIME vs. STORETIME

CHARTTIME is the time at which a measurement is charted. In almost all cases, this is the time which best matches the time of actual measurement. In the case of continuous vital signs (heart rate, respiratory rate, invasive blood pressure, non-invasive blood pressure, oxygen saturation), the CHARTTIME is usually exactly the time of measurement. STORETIME is the time at which the data is recorded in the database: logically it occurs after CHARTTIME, often by hours, but usually not more than that.

CHARTDATE

CHARTDATE is equivalent to CHARTTIME, except it does not contain any information on the time (all hour, minute, and seconds are 0 for these measurements).

ADMITTIME, DISCHTIME, DEATHTIME

ADMITTIME and DISCHTIME are the hospital admission and discharge times, respectively. DEATHTIME is the time of death of a patient if they died in hospital. If the patient did not die within the hospital for the given hospital admission, DEATHTIME will be null.

CREATETIME, UPDATETIME, ACKNOWLEDGETIME, OUTCOMETIME, FIRSTRESERVATIONTIME, CURRENTRESERVATIONTIME

CREATETIME is the time at which an ICU discharge was requested for a given patient. UPDATETIME is the time which the ICU discharge request was updated. ACKNOWLEDGETIME was the time at which the discharge request was acknowledged by the transfers team. OUTCOMETIME is the time at which the ICU discharge request was completed (with an outcome of ‘Discharged’ or ‘Canceled’). FIRSTRESERVATIONTIME and CURRENTRESERVATIONTIME only occur for patients who require certain locations in the hospital.

INTIME, OUTTIME

INTIME and OUTTIME provide the time at which a patient entered and exited the given unit. In the ICUSTAYS table, the unit is always an ICU. In the TRANSFERS table, the unit can be any ward in the hospital.

STARTTIME, ENDTIME

For events which occur over a period of time, STARTTIME and ENDTIME provide the beginning and end time of the event. For medical infusions, these columns indicate the period over which the substance was administered.

COMMENTS_DATE

COMMENTS_DATE provides the time at which a cancel or edit comment was made for a given order.

DOB, DOD, DOD_HOSP, DOD_SSN

DOB is the patient’s date of birth. If the patient is older than 89, their date of birth is set to 300 at their first admission. DOD is the patient’s date of death: sourced either from the hospital database (DOD_HOSP) or the social security database (DOD_SSN).

TRANSFERTIME

TRANSFERTIME is the time at which the patient’s service changes.