5. Reference¶
The entries in this section provide a complete reference to all datatypes and functions of the MGRID Healthcare Datatype Library.
5.1. HDM RIM implementation¶
The MGRID HDM is a database implementation of the HL7v3 RIM. This section describes how class relations, class connections and datatypes are implemented for the PostgreSQL database servers.
5.1.1. Classes and attributes¶
RIM classes are implemented with database tables. Each class attribute
is implemented with a column in the corresponding database table.
Besides columns for the class attributes, additional columns are added
to each table. The additional columns are prepended
by _
.
_id
is the internal id used by the system. Upon insertion of new records, the database server will assign numbers to this column from the sequence"InfrastructureRoot__id_seq"
. Since every other RIM class is a child class of InfrastructureRoot, all other tables in the RIM have the_id
column as well, and are associated with the sequence"InfrastructureRoot__id_seq"
._mif
is a column storing meta information about the message received. It is populated by the message parsers generated with the MGRID Messaging SDK._clonename
is a column storing meta information about the message received. It is populated by the message parsers generated with the MGRID Messaging SDK.
You can use the psql \d
command to inspect tables.
lake=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------------------------+----------+--------
public | Access | table | hcuser
public | Account | table | hcuser
public | Acknowledgement | table | hcuser
public | AcknowledgementDetail | table | hcuser
public | Act | table | hcuser
public | ActRelationship | table | hcuser
public | Attachment | table | hcuser
public | AttentionLine | table | hcuser
public | Batch | table | hcuser
...
public | SubstanceAdministration | table | hcuser
public | Supply | table | hcuser
public | Transmission | table | hcuser
public | TransmissionRelationship | table | hcuser
public | Transmission_CommunicationFunction | table | hcuser
public | WorkingList | table | hcuser
(62 rows)
The InfrastructureRoot
is the root class of the RIM.
\d "InfrastructureRoot"
Unlogged table "rim2011.InfrastructureRoot"
Column | Type | Modifiers
-----------------+--------------------------+--------------------------------------------------------------------
_id | bigint | not null default nextval('"InfrastructureRoot__id_seq"'::regclass)
_mif | text |
_clonename | text |
nullFlavor | cv('NullFlavor','CNE') |
realmCode | set('Realm') |
typeId | "II" |
templateId | "LIST_II" |
Indexes:
"InfrastructureRoot_pkey" PRIMARY KEY, btree (_id)
Number of child tables: 17 (Use \d+ to list them.)
5.1.2. Class hierarchy¶
The class hierarchy is implemented with PostgreSQL table inheritance. Using table inheritance has as benefit that when a top level table is queried or updated, the query or update will incorporate rows of inheritance child tables. The drawback of using inheritance is that it is not possible to have the database server automatically enforce foreign key constraints.
5.1.3. Class connections¶
Connections between RIM classes, such as the connection
between Role
and Entity
, are
implemented by additional columns. These columns are added to the table
of the connection side with maximum multiplicity = 1, using the name of
the connection for that connection side. In the table
below, player
and scoper
have been
added to persist the connections to the corresponding records in
the Entity
table hierarchy, referencing
the Entity._id
column.
\d "Role"
Unlogged table "rim2011.Role"
Column | Type | Modifiers
----------------------------+--------------------------+--------------------------------------------------------------------
_id | bigint | not null default nextval('"InfrastructureRoot__id_seq"'::regclass)
_mif | text |
_clonename | text |
nullFlavor | "CS" |
realmCode | set |
typeId | "II" |
templateId | "LIST_II" |
player | bigint |
scoper | bigint |
...
5.1.4. Complex and simple types in the HDM¶
Most table columns have a complex type as their column type. For some
types the simple type version was chosen to implement RIM table columns.
Use \d <tablename>
to check the type of a column.
5.2. Complex Types¶
All Healthcare Datatypes have ‘Complex Type’ implementation, which are types with a JSON literal form. On input, valid keys and value types will be checked. All complex types are indexable using Gist and Gin indexes, for fast lookups with certain operator symbols.
The complex types R1 are listed in List of Complex Types R1. The complex types R2 are listed in List of Complex Types R2. The differences between simple and complex Healthcare Datatypes are described in Complex and Simple types.
5.2.1. Installation¶
Before the complex types can be used, they must be installed in a database by running a separate SQL script, which is described in Administrators Guide.
5.2.2. Operators on complex types¶
Table Operators on complex types lists the operators that can be used to get elements from complex type, query if a complex type contains a value, and operators to construct complex types.
5.2.3. Operators on complex types¶
Operat or | Right operand type | Return s | Description |
---|---|---|---|
-> | integer | ANY | Get complex type array element, indexed from 0. |
->> | integer | text | Get complex type array element, indexed from 0. |
-> | text | ANY | Get complex type property on key name. |
->> | text | text | Get complex type property on key name. |
@> | ANY | bool | Returns true if the left value contains the right value. |
<@ | ANY | bool | Returns true if the left value is contained in the right value. |
= | ANY | bool | Equality on a complex type. The equality on ANY and all subtypes is defined as equal iff all compositional properties are equal (identity). |
<> | ANY | bool | Inequality on a complex type. |
|| | ANY | SET | Append two complex types to form a collection complex type. If the left argument complex type is an array, the right argument is appended to that array. If the left argument is an object, a new array is formed with the left and right arguments as respective elements. |
Append two complex types to form a collection complex type. If the left argument complex type is an array, the right argument is appended to that array. If the left argument is an object, a new array is formed with the left and right arguments as respective elements.
5.2.4. Complex Data Value Instantiation¶
Complex values can be created either by input of a JSON string literal, or a more convenient way by using the supplied ‘..inval’ functions, that given the types compositional properties as input, generates a JSON literal value. These ‘..inval’ functions can be used by automated HL7v3 XML transformers to automatically create SQL, such as MGRID’s CDA R2 and HL7v3 Clinical Statement transformers.
As these examples show, constructing complex types can result in large expressions. Under normal circumstances, these expressions are generated by programs such as the message parser generater described in chapter Importing xml messages, not by database users manually.
5.2.4.1. Instantiating complex types¶
lake=# SELECT enxpinval("content" := 'John', "partType" := 'GIV');
enxpinval
----------------------------------------------------------------------------------------------------------------------------------------------------
{"content": "John", "dataType": "ENXP", "partType": "GIV", "mediaType": "text/plain", "representation": "TXT", "integrityCheckAlgorithm": "SHA-1"}
(1 row)
5.2.4.2. Pretty printing complex types¶
With the PostgreSQL 9.5+ function jsonb_pretty(jsonb)
it is possible to pretty print a JSON blob.
We can cast any complex type to ANY
, and ANY
can be cast to jsonb
, after which the value can be pretty
printed.
lake=# SELECT jsonb_pretty(enxpinval("content" := 'John', "partType" := 'GIV')::"ANY"::jsonb);
jsonb_pretty
----------------------------------------
{ +
"content": "John", +
"dataType": "ENXP", +
"partType": "GIV", +
"mediaType": "text/plain", +
"representation": "TXT", +
"integrityCheckAlgorithm": "SHA-1"+
}
(1 row)
5.2.4.3. Complex array instantiating and ..inval() function nesting¶
This example shows a number of things. The entity name use is
specified with the json array literal form ["L"]
. The
family and given names are sets of entity name parts. These are
instantiated by calling enxpinval
. For the given
name, two enxp values are joined to form a json array. (SET_ENXP)
lake=# SELECT jsonb_pretty(eninval(
"use" := '["L"]'::"set_EntityNameUse",
"family" := enxpinval("content" := 'Doe'),
"given" := enxpinval("content" := 'John')
|| enxpinval("content" := 'Anthony'))::"ANY"::jsonb);
jsonb_pretty
------------------------------------------------
{ +
"use": [ +
"L" +
], +
"given": [ +
{ +
"content": "John", +
"dataType": "ENXP", +
"mediaType": "text/plain", +
"representation": "TXT", +
"integrityCheckAlgorithm": "SHA-1"+
}, +
{ +
"content": "Anthony", +
"dataType": "ENXP", +
"mediaType": "text/plain", +
"representation": "TXT", +
"integrityCheckAlgorithm": "SHA-1"+
} +
], +
"family": [ +
{ +
"content": "Doe", +
"dataType": "ENXP", +
"mediaType": "text/plain", +
"representation": "TXT", +
"integrityCheckAlgorithm": "SHA-1"+
} +
], +
"dataType": "EN" +
}
(1 row)
5.2.4.4. Complex type array instantiating of a GTS value¶
The following example shows the translation, by the CCDA message parser (see the documentation of the MGRID MSG SDK for more information on generating parsers that convert from XML to SQL), of the following effectiveTime, that is the intersection (SetOperator = A) of a IVL_TS and a PIVL_TS:
<effectiveTime xsi:type="IVL_TS">
<low value="20120512"/>
<high value="20120512"/>
</effectiveTime>
<effectiveTime xsi:type="PIVL_TS" institutionSpecified="true" operator="A">
<period value="1" unit="h"/>
</effectiveTime>
lake=# CREATE TABLE t AS SELECT
lake-# ivl_tsinval(
lake(# "high" := ivxb_tsinval("inclusive" := 'true', "value" := '20120512'),
lake(# "low" := ivxb_tsinval("inclusive" := 'true', "value" := '20120512'),
lake(# "operator" := 'I')
lake-# ||
lake-# pivl_tsinval(
lake(# "institutionSpecified" := True,
lake(# "operator" := 'A',
lake(# "period" := pqinval("unit" := 'h', "value" := '1')) AS a;
SELECT 1
lake=# SELECT jsonb_pretty(a::"ANY"::jsonb) FROM t;
jsonb_pretty
--------------------------------------
[ +
{ +
"low": { +
"value": "20120512", +
"dataType": "IVXB_TS", +
"inclusive": true +
}, +
"high": { +
"value": "20120512", +
"dataType": "IVXB_TS", +
"inclusive": true +
}, +
"dataType": "IVL_TS", +
"operator": "I" +
}, +
{ +
"period": { +
"unit": "h", +
"value": 1, +
"dataType": "PQ" +
}, +
"dataType": "PIVL_TS", +
"operator": "A", +
"institutionSpecified": true+
} +
]
(1 row)
The type of the operator
property
of PIVL_TS
is SetOperator
. This is a vocabulary type, with valid
values the elements from the codesystem SetOperator
:
select codesystem('SetOperator');
codesystem
--------------------------------------
convex hull (H)
intersect (A)
periodic hull (P)
ValueSetOperator (_ValueSetOperator)
exclude (E)
include (I)
(6 rows)
5.2.5. Complex Data Value Accessing¶
Properties of a complex data value can be accessed with
the ->
and ->>
operators. These
operators take the name of the property or the index in the array as
argument, and return the property as ANY
or text
, respectively.
5.2.5.1. Accessing complex data array alements¶
This example continues from the previous one where the
table t
was created.
Arrays in complex types (SET, COLL, BAG, LIST) are indexed from 0. Select the first element.
lake=# select a->0 from t;
?column?
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"low": {"value": "20120512", "dataType": "IVXB_TS", "inclusive": true}, "high": {"value": "20120512", "dataType": "IVXB_TS", "inclusive": true}, "dataType": "IVL_TS", "operator": "I"}
(1 row)
Select the object at path 0,low,value
using the jsonb path operator #>
. Note that this is an operator
on jsonb
so we need to cast to jsonb first.
lake=# select a::"ANY"::jsonb#>>'{0,low,value}' from t;
?column?
----------
20120512
(1 row)
The example above is simple json navigation and selecting elements. A more powerful translation is available through the
cast from IVL_TS
to ivl_ts
, after which the full range of ivl_ts operators is available.
lake=# select (a->0)::"IVL_TS"::ivl_ts from t;
ivl_ts
---------------------
[20120512;20120512]
(1 row)
5.2.5.2. Appending operators to select paths¶
Select from the second element the period property, and select from the period
the value. Note that every operator but the last is the ANY
returning operator ->
, and the last operator is the text
returning operator ->>
.
select a->1->'period'->>'value' from t;
?column?
----------
1
(1 row)
The -> operator returns ANY.
select pg_typeof(a->1->'period') from t;
pg_typeof
-----------
"ANY"
(1 row)
To enable calculation with pq, cast the period to PQ then to pq.
select (a->1->'period')::"PQ"::pq from t;
pq
-----
1 h
(1 row)
5.2.5.3. Does complex data value contain a value?¶
This example continues from the previous one where the
table t
was created.
Use the <@
and @>
operators to
query if a complex value contains another complex value. The example
below shows how to select only the effectivetimes that have a period of
one hour.
select (a->0)::"IVL_TS"::ivl_ts
from t
where a @> '[{"period":{"unit":"h"}}]';
ivl_ts
---------------------
[20120512;20120512]
(1 row)
5.2.6. List of Complex Types R1¶
Mnemonic | Schema | Description |
---|---|---|
AD | r1 | Mailing and home or office addresses. A sequence of address parts, such as street or post office Box, city, postal code, country, etc. |
ADXP | r1 | A character string that may have a type-tag signifying its role in the address. Typical parts that exist in about every address are street, house number, or post box, postal code, city, country but other roles may be defined regionally, nationally, or on an enterprise level (e.g. in military addresses). Addresses are usually broken up into lines, which are indicated by special line-breaking delimiter elements (e.g., DEL). |
BIN | r1 | Binary data is a raw block of bits. Binary data is a protected type that MUST not be used outside the data type specification. |
BL | r1 | The Boolean type stands for the values of two-valued logic. A Boolean value can be either true or false, or, as any other value may be NULL. |
BN | r1 | The BooleanNonNull type is used where a Boolean cannot have a null value. A Boolean value can be either true or false. |
BXIT_CD | r1 | The quantity in which the bag item occurs in its containing bag. |
BXIT_IVL_PQ | r1 | The quantity in which the bag item occurs in its containing bag. |
CD | r1 | A concept descriptor represents any kind of concept usually by giving a code defined in a code system. A concept descriptor can contain the original text or phrase that served as the basis of the coding and one or more translations into different coding systems. A concept descriptor can also contain qualifiers to describe, e.g., the concept of a “left foot” as a postcoordinated term built from the primary code “FOOT” and the qualifier “LEFT”. In exceptional cases, the concept descriptor need not contain a code but only the original text describing that concept. |
CE | r1 | Coded data, consists of a coded value (CV) and, optionally, coded value(s) from other coding systems that identify the same concept. Used when alternative codes may exist. |
CO | r1 | Coded data, where the domain from which the codeset comes is ordered. The Coded Ordinal data type adds semantics related to ordering so that models that make use of such domains may introduce model elements that involve statements about the order of the terms in a domain. |
CR | r1 | A concept qualifier code with optionally named role. Both qualifier role and value codes must be defined by the coding system. For example, if SNOMED RT defines a concept “leg”, a role relation “has-laterality”, and another concept “left”, the concept role relation allows to add the qualifier “has-laterality: left” to a primary code “leg” to construct the meaning “left leg”. |
CS | r1 | Coded data, consists of a code, display name, code system, and original text. Used when a single code value must be sent. |
CV | r1 | Coded data, consists of a code, display name, code system, and original text. Used when a single code value must be sent. |
ED | r1 | Data that is primarily intended for human interpretation or for further machine processing is outside the scope of HL7. This includes unformatted or formatted written language, multimedia data, or structured information as defined by a different standard (e.g., XML-signatures.) Instead of the data itself, an ED may contain only a reference (see TEL.) Note that the ST data type is a specialization of when the is text/plain. |
EIVL_PPD_TS | r1 | Note: because this type is defined as an extension of SXCM_T, all of the attributes and elements accepted for T are also accepted by this definition. However, they are NOT allowed by the normative description of this type. Unfortunately, we cannot write a general purpose schematron contraints to provide that extra validation, thus applications must be aware that instance (fragments) that pass validation with this might might still not be legal. |
EIVL_TS | r1 | Note: because this type is defined as an extension of SXCM_T, all of the attributes and elements accepted for T are also accepted by this definition. However, they are NOT allowed by the normative description of this type. Unfortunately, we cannot write a general purpose schematron contraints to provide that extra validation, thus applications must be aware that instance (fragments) that pass validation with this might might still not be legal. |
EN | r1 | A name for a person, organization, place or thing. A sequence of name parts, such as given name or family name, prefix, suffix, etc. Examples for entity name values are “Jim Bob Walton, Jr.”, “Health Level Seven, Inc.”, “Lake Tahoe”, etc. An entity name may be as simple as a character string or may consist of several entity name parts, such as, “Jim”, “Bob”, “Walton”, and “Jr.”, “Health Level Seven” and “Inc.”, “Lake” and “Tahoe”. |
ENXP | r1 | A character string token representing a part of a name. May have a type code signifying the role of the part in the whole entity name, and a qualifier code for more detail about the name part type. Typical name parts for person names are given names, and family names, titles, etc. |
GLIST_PQ | r1 | This is the start-value of the generated list. |
GLIST_TS | r1 | This is the start-value of the generated list. |
HXIT_CE | r1 | The time interval during which the given information was, is, or is expected to be valid. The interval can be open or closed, as well as infinite or undefined on either side. |
HXIT_PQ | r1 | The time interval during which the given information was, is, or is expected to be valid. The interval can be open or closed, as well as infinite or undefined on either side. |
II | r1 | An identifier that uniquely identifies a thing or object. Examples are object identifier for HL7 RIM objects, medical record number, order id, service catalog item id, Vehicle Identification Number (VIN), etc. Instance identifiers are defined based on ISO object identifiers. |
INT | r1 | Integer numbers (-1,0,1,2, 100, 3398129, etc.) are precise numbers that are results of counting and enumerating. Integer numbers are discrete, the set of integers is infinite but countable. No arbitrary limit is imposed on the range of integer numbers. Two NULL flavors are defined for the positive and negative infinity. |
IVL_INT | r1 | The low limit of the interval. |
IVL_MO | r1 | The low limit of the interval. |
IVL_PPD_PQ | r1 | The low limit of the interval. |
IVL_PPD_TS | r1 | The low limit of the interval. |
IVL_PQ | r1 | The low limit of the interval. |
IVL_REAL | r1 | The low limit of the interval. |
IVL_TS | r1 | The low limit of the interval. |
IVXB_INT | r1 | Specifies whether the limit is included in the interval (interval is closed) or excluded from the interval (interval is open). |
IVXB_MO | r1 | Specifies whether the limit is included in the interval (interval is closed) or excluded from the interval (interval is open). |
IVXB_PPD_PQ | r1 | Specifies whether the limit is included in the interval (interval is closed) or excluded from the interval (interval is open). |
IVXB_PPD_TS | r1 | Specifies whether the limit is included in the interval (interval is closed) or excluded from the interval (interval is open). |
IVXB_PQ | r1 | Specifies whether the limit is included in the interval (interval is closed) or excluded from the interval (interval is open). |
IVXB_REAL | r1 | Specifies whether the limit is included in the interval (interval is closed) or excluded from the interval (interval is open). |
IVXB_TS | r1 | Specifies whether the limit is included in the interval (interval is closed) or excluded from the interval (interval is open). |
MO | r1 | A monetary amount is a quantity expressing the amount of money in some currency. Currencies are the units in which monetary amounts are denominated in different economic regions. While the monetary amount is a single kind of quantity (money) the exchange rates between the different units are variable. This is the principle difference between physical quantity and monetary amounts, and the reason why currency units are not physical units. |
ON | r1 | A name for an organization. A sequence of name parts. |
PIVL_PPD_TS | r1 | Note: because this type is defined as an extension of SXCM_T, all of the attributes and elements accepted for T are also accepted by this definition. However, they are NOT allowed by the normative description of this type. Unfortunately, we cannot write a general purpose schematron contraints to provide that extra validation, thus applications must be aware that instance (fragments) that pass validation with this might might still not be legal. |
PIVL_TS | r1 | Note: because this type is defined as an extension of SXCM_T, all of the attributes and elements accepted for T are also accepted by this definition. However, they are NOT allowed by the normative description of this type. Unfortunately, we cannot write a general purpose schematron contraints to provide that extra validation, thus applications must be aware that instance (fragments) that pass validation with this might might still not be legal. |
PN | r1 | A name for a person. A sequence of name parts, such as given name or family name, prefix, suffix, etc. PN differs from EN because the qualifier type cannot include LS (Legal Status). |
PPD_PQ | r1 | The primary measure of variance/uncertainty of the value (the square root of the sum of the squares of the differences between all data points and the mean). The standard deviation is used to normalize the data for computing the distribution function. Applications that cannot deal with probability distributions can still get an idea about the confidence level by looking at the standard deviation. |
PPD_TS | r1 | The primary measure of variance/uncertainty of the value (the square root of the sum of the squares of the differences between all data points and the mean). The standard deviation is used to normalize the data for computing the distribution function. Applications that cannot deal with probability distributions can still get an idea about the confidence level by looking at the standard deviation. |
PQ | r1 | A dimensioned quantity expressing the result of a measurement act. |
PQR | r1 | A representation of a physical quantity in a unit from any code system. Used to show alternative representation for a physical quantity. |
QTY | r1 | is an abstract generalization for all data types (1) whose value set has an order relation (less-or-equal) and (2) where difference is defined in all of the data type’s totally ordered value subsets. The quantity type abstraction is needed in defining certain other types, such as the interval and the probability distribution. |
REAL | r1 | Fractional numbers. Typically used whenever quantities are measured, estimated, or computed from other real numbers. The typical representation is decimal, where the number of significant decimal digits is known as the precision. Real numbers are needed beyond integers whenever quantities of the real world are measured, estimated, or computed from other real numbers. The term “Real number” in this specification is used to mean that fractional values are covered without necessarily implying the full set of the mathematical real numbers. |
RTO | r1 | A quantity constructed as the quotient of a numerator quantity divided by a denominator quantity. Common factors in the numerator and denominator are not automatically cancelled out. supports titers (e.g., “1:128”) and other quantities produced by laboratories that truly represent ratios. Ratios are not simply “structured numerics”, particularly blood pressure measurements (e.g. “120/60”) are not ratios. In many cases REAL should be used instead of . |
RTO_MO_PQ | r1 | The quantity that is being divided in the ratio. The default is the integer number 1 (one). |
RTO_PQ_PQ | r1 | The quantity that is being divided in the ratio. The default is the integer number 1 (one). |
RTO_QTY_QTY | r1 | The quantity that is being divided in the ratio. The default is the integer number 1 (one). |
SC | r1 | An ST that optionally may have a code attached. The text must always be present if a code is present. The code is often a local code. |
SLIST_PQ | r1 | The origin of the list item value scale, i.e., the physical quantity that a zero-digit in the sequence would represent. |
SLIST_TS | r1 | The origin of the list item value scale, i.e., the physical quantity that a zero-digit in the sequence would represent. |
ST | r1 | The character string data type stands for text data, primarily intended for machine processing (e.g., sorting, querying, indexing, etc.) Used for names, symbols, and formal expressions. |
SXCM_CD | r1 | A code specifying whether the set component is included (union) or excluded (set-difference) from the set, or other set operations with the current set component and the set as constructed from the representation stream up to the current point. |
SXCM_INT | r1 | A code specifying whether the set component is included (union) or excluded (set-difference) from the set, or other set operations with the current set component and the set as constructed from the representation stream up to the current point. |
SXCM_MO | r1 | A code specifying whether the set component is included (union) or excluded (set-difference) from the set, or other set operations with the current set component and the set as constructed from the representation stream up to the current point. |
SXCM_PPD_PQ | r1 | A code specifying whether the set component is included (union) or excluded (set-difference) from the set, or other set operations with the current set component and the set as constructed from the representation stream up to the current point. |
SXCM_PPD_TS | r1 | A code specifying whether the set component is included (union) or excluded (set-difference) from the set, or other set operations with the current set component and the set as constructed from the representation stream up to the current point. |
SXCM_PQ | r1 | A code specifying whether the set component is included (union) or excluded (set-difference) from the set, or other set operations with the current set component and the set as constructed from the representation stream up to the current point. |
SXCM_REAL | r1 | A code specifying whether the set component is included (union) or excluded (set-difference) from the set, or other set operations with the current set component and the set as constructed from the representation stream up to the current point. |
SXCM_TS | r1 | A code specifying whether the set component is included (union) or excluded (set-difference) from the set, or other set operations with the current set component and the set as constructed from the representation stream up to the current point. |
SXPR_TS | r1 | |
TEL | r1 | A telephone number (voice or fax), e-mail address, or other locator for a resource (information or service) mediated by telecommunication equipment. The address is specified as a URL qualified by time specification and use codes that help in deciding which address to use for a given time and purpose. |
TN | r1 | A restriction of entity name that is effectively a simple string used for a simple name for things and places. |
TS | r1 | A quantity specifying a point on the axis of natural time. A point in time is most often represented as a calendar expression. |
URL | r1 | A telecommunications address specified according to Internet standard RFC 1738 [http://www.ietf.org/rfc/rfc1738.txt]. The URL specifies the protocol and the contact point defined by that protocol for the resource. Notable uses of the telecommunication address data type are for telephone and telefax numbers, e-mail addresses, Hypertext references, FTP references, etc. |
UVP_TS | r1 | The probability assigned to the value, a decimal number between 0 (very uncertain) and 1 (certain). |
5.2.7. List of Complex Types R2¶
Mnemonic | Schema | Description |
---|---|---|
AD | r2 | Mailing and home or office addresses. AD is primarily used to communicate data that will allow printing mail labels, or that will allow a person to physically visit that address. The postal address datatype is not supposed to be a container for additional information that might be useful for finding geographic locations (e.g., GPS coordinates) or for performing epidemiological studies. Such additional information should be captured by other, more appropriate data structures. Addresses are essentially sequences of address parts, but add a “use” code and a valid time range for information about if and when the address can be used for a given purpose. |
ADXP | r2 | A part that may have a type-tag signifying its role in the address. Typical parts that exist in about every address are street, house number, or post box, postal code, city, country but other roles may be defined regionally, nationally, or on an enterprise level (e.g. in military addresses). Addresses are usually broken up into lines, which may be indicated by special line-breaking delimiter elements (e.g., DEL). |
BL | r2 | BL stands for the values of two-valued logic. A BL value can be either true or false, or may have a nullFlavor. |
CD | r2 | A CD is a reference to a concept defined in an external code system, terminology, or ontology. A CD may contain a simple code - that is, a reference to a concept defined directly by the referenced code system, or it may contain an expression in some syntax defined by the referenced code system that can be meaningfully evaluated. e.g., the concept of a “left foot” as a postcoordinated term built from the primary code “FOOT” and the qualifier “LEFT”. A CD may also contain an original text or phrase that served as the basis of the coding. This is preserved to allow for validation of the representation of the concept in various fashions. A CD can contain one or more translations into multiple coding systems. The translations are all representations of the same concept in various code systems. There is only one concept, and only the first CD may contain an original text. It is possible to represent the translation chain - which CD was translated from which - if desired. Each CD may also carry a rationale to indicate why it is represented. A CD with no nullFlavor attribute SHALL have a code attribute or nonNull originalText attribute. A CD that has a code, codeSystem or originalText attribute but does not meet external constraints of the applicable value set SHALL have a nullFlavor attribute with a value of “OTH”. Attributes with type CD are generally bound by externally specified constraints which constrain the coded concepts to which a CD may refer. These constraints may be qualified as “extensible” (CWE) or “not extensible” (CNE). If the constraint is not extensible (CNE), then a the CD that does not have a nullFlavor SHALL contain a code that conforms to the constraint. If the constraint is extensible (CWE) then a CD that does not have a nullFlavor SHALL contain either a code that exists in the domain with which the attribute is associated, a code from a locally defined code system, or just some originalText that describes the concept. If the code is taken from a locally defined code system, then the codeSystem property SHALL specify the local code system. For both CNE and CWE constraint types, the translations may contain nonNull codes from any source unless otherwise specified by the constraining model. For code systems that define expression syntaxes, CNE constraints may be used, providing that the code system definitions define the appropriate support to enable value sets to make useful statements about how to control the expression syntax, and that the value set machinery used also has the appropriate support |
CO | r2 | Represents data where coded values are associated with a specific order. Note: CO may be used for things that model rankings and scores, e.g. likert scales, pain, Apgar values, etc, where there is a) implied ordering, b) no implication that the distance between each value is constant, and c) the total number of values is finite. CO may also be used in the context of an ordered code system. In this case, it may not be appropriate or even possible to use the value attribute, but CO may still be used so that models that make use of such code systems may introduce model elements that involve statements about the order of the terms in a domain. The relative order of values in a code system need not be independently obvious in the literal representation of the CO. It these circumstances, is expected that an application will look up the ordering of these values from some definition of the code system. Some of the code systems will directly assign numerical value to the concepts that are suitable for some mathemetical operations. Though it would generally make sense, applications SHOULD not assume that the translations of the code, if provided, will have the same ordering as the CO. Translations SHALL not be considered when the ordering of the code system is determined. |
CS | r2 | Coded data in its simplest form, where only the code is not predetermined. The code system and code system version are implied and fixed by the context in which the CS value occurs. Due to its highly restricted functionality, CS SHALL only be used for simple structural attributes with highly controlled and stable terminologies where:
|
ED | r2 | Data that is primarily intended for human interpretation or for further machine processing outside the scope of this specification. This includes unformatted or formatted written language, multimedia data, or structured information as defined by a different standard (e.g., XML-signatures.) Encapsulated data can be present in two forms, inline or by reference. The content is the same whether it is located inline or remote.Inline data is communicated or moved as part of the encapsulated data value, whereas by-reference data may reside at a different location: a URL/URI that provides reference to the information required to locate the data. Inline data may be provided in one of 3 different ways:
Content SHALL be provided if the ED has no nullFlavor. The content may be provided in-line (using only one of value, data or xml), or it may be provided as a reference.Content may be provided in-line and a reference also may be given; in these cases, it is expected that the content of the reference will be exactly the same as the in-line content. Information Processing Entities are not required to check this, but may regard it as an error condition if the content does not match |
EIVL_TS | r2 | Specifies a periodic interval of time where the recurrence is based on activities of daily living or other important events that are time-related but not fully determined by time. Example: “one hour after breakfast” specifies the beginning of the interval at one hour after breakfast is finished. Breakfast is assumed to occur before lunch but is not determined to occur at any specific time |
EN | r2 | A name for a person, organization, place or thing. Examples: “Jim Bob Walton, Jr.”, “Health Level Seven, Inc.”, “Lake Tahoe”, etc. An entity name may be as simple as a character string or may consist of several entity name parts, such as, “Jim”, “Bob”, “Walton”, and “Jr.”, “Health Level Seven” and “Inc.”. Entity names are essentially sequences of entity name parts, but add a “use” code and a valid time range for information about when the name was used and how to choose between multiple aliases that may be valid at the same point in time. |
ENXP | r2 | A part that may have a type code signifying the role of the part in the whole entity name, and qualifier codes for more detail about the name part type. (Typical name parts for person names are given names, and family names, titles, etc. ). |
GLIST_PQ | r2 | A periodic or monotone sequence of values generated from a few parameters, rather than being enumerated. Used to specify regular sampling points for biosignals. |
GLIST_REAL | r2 | A periodic or monotone sequence of values generated from a few parameters, rather than being enumerated. Used to specify regular sampling points for biosignals. |
GLIST_TS | r2 | A periodic or monotone sequence of values generated from a few parameters, rather than being enumerated. Used to specify regular sampling points for biosignals. |
HXIT | r2 | Information about the history of this value: period of validity and a reference to an identified event that established this value as valid. Because of the way that the types are defined, a number of attributes of the datatypes have values with a type derived from HXIT. In these cases the HXIT attributes are constrained to null. The only case where the HXIT attributes are allowed within a datatype is on items in a collection (DSET, LIST, BAG, HIST). The use of these attributes is generally subject to further constraints in the specifications that make use of these types. |
II | r2 | An identifier that uniquely identifies a thing or object. Examples are object identifier for HL7 RIM objects, medical record number, order id, service catalog item id, Vehicle Identification Number (VIN), etc. Instance identifiers are usually defined based on ISO object identifiers. An identifier allows someone to select one record, object or thing from a set of candidates. Usually an identifier alone without any context is not usable. Identifiers are distinguished from concept descriptors as concept descriptors never identify an individual thing, although there may sometimes be an individual record or object that represents the concept. Information Processing Entities claiming direct or indirect conformance SHALL never assume that receiving applications can infer the identity of issuing authority or the type of the identifier from the identifier or components thereof. |
INT | r2 | Integer numbers (-1,0,1,2, 100, 3398129, etc.) are precise numbers that are results of counting and enumerating. Integer numbers are discrete, the set of integers is infinite but countable. No arbitrary limit is imposed on the range of integer numbers. |
IVL_CO | r2 | A set of consecutive values of an ordered base datatype. Any ordered type can be the basis of an IVL; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the IVL must be elements of a totally ordered subset of the partially ordered datatype. For example, PQ is considered ordered. However the ordering of PQs is only partial; a total order is only defined among comparable quantities (quantities of the same physical dimension). While IVLs between 2 and 4 meter exists, there is no IVL between 2 meters and 4 seconds |
IVL_INT | r2 | A set of consecutive values of an ordered base datatype. Any ordered type can be the basis of an IVL; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the IVL must be elements of a totally ordered subset of the partially ordered datatype. For example, PQ is considered ordered. However the ordering of PQs is only partial; a total order is only defined among comparable quantities (quantities of the same physical dimension). While IVLs between 2 and 4 meter exists, there is no IVL between 2 meters and 4 seconds |
IVL_MO | r2 | A set of consecutive values of an ordered base datatype. Any ordered type can be the basis of an IVL; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the IVL must be elements of a totally ordered subset of the partially ordered datatype. For example, PQ is considered ordered. However the ordering of PQs is only partial; a total order is only defined among comparable quantities (quantities of the same physical dimension). While IVLs between 2 and 4 meter exists, there is no IVL between 2 meters and 4 seconds |
IVL_PQ | r2 | A set of consecutive values of an ordered base datatype. Any ordered type can be the basis of an IVL; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the IVL must be elements of a totally ordered subset of the partially ordered datatype. For example, PQ is considered ordered. However the ordering of PQs is only partial; a total order is only defined among comparable quantities (quantities of the same physical dimension). While IVLs between 2 and 4 meter exists, there is no IVL between 2 meters and 4 seconds |
IVL_QTY | r2 | A set of consecutive values of an ordered base datatype. Any ordered type can be the basis of an IVL; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the IVL must be elements of a totally ordered subset of the partially ordered datatype. For example, PQ is considered ordered. However the ordering of PQs is only partial; a total order is only defined among comparable quantities (quantities of the same physical dimension). While IVLs between 2 and 4 meter exists, there is no IVL between 2 meters and 4 seconds |
IVL_REAL | r2 | A set of consecutive values of an ordered base datatype. Any ordered type can be the basis of an IVL; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the IVL must be elements of a totally ordered subset of the partially ordered datatype. For example, PQ is considered ordered. However the ordering of PQs is only partial; a total order is only defined among comparable quantities (quantities of the same physical dimension). While IVLs between 2 and 4 meter exists, there is no IVL between 2 meters and 4 seconds |
IVL_TS | r2 | A set of consecutive values of an ordered base datatype. Any ordered type can be the basis of an IVL; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the IVL must be elements of a totally ordered subset of the partially ordered datatype. For example, PQ is considered ordered. However the ordering of PQs is only partial; a total order is only defined among comparable quantities (quantities of the same physical dimension). While IVLs between 2 and 4 meter exists, there is no IVL between 2 meters and 4 seconds |
MO | r2 | A MO is a quantity expressing the amount of money in some currency. Currencies are the units in which monetary amounts are denominated in different economic regions. While the monetary amount is a single kind of quantity (money) the exchange rates between the different units are variable. This is the principle difference between PQ and MO, and the reason why currency units are not physical units. |
NPPD_AD | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_BL | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_CD | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_CO | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_CS | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_ED | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_EN | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_II | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_INT | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_MO | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_PQ | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_REAL | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_RTO | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_SC | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_ST | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_TEL | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
NPPD_TS | r2 | Specializes ANY Parameter: T : ANY A set of UVP with probabilities (also known as a histogram.) All the elements in the set are considered alternatives and are rated each with its probability expressing the belief (or frequency) that each given value holds. NPPD<T> may be used where only one value for T may be true. The sum of the probabilities should be <= 1, but due to estimating and rounding inaccuracies, the total may actually exceed 1 |
PIVL_TS | r2 | An interval of time that recurs periodically. PIVL has two properties, phase and period/frequency. phase specifies the “interval prototype” that is repeated on the period/frequency. |
PQ | r2 | A dimensioned quantity expressing the result of measuring |
PQR | r2 | Specializes CD.CV An extension of the coded value datatype representing a physical quantity using a unit from any code system. Used to show alternative representation for a physical quantity. The coded value represents the unit (usually in some other coding system than UCUM). |
QSC_TS | r2 | Specializes QSET Specifies a QSET as an coded value that describes a predefined QSET(TS). |
QSD_PQ | r2 | Specializes QSET Specifies a QSET as the difference between two sets. |
QSD_TS | r2 | Specializes QSET Specifies a QSET as the difference between two sets. |
QSET_CO | r2 | Abstract; specializes ANY Parameter: T : QTY An unordered set of distinct values which are quantities. Any ordered type can be the basis of an QSET; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the QSET must be elements of a totally ordered subset of the partially ordered datatype (for example, PQ is only ordered when the units are consistent. Every value in a QSET(PQ) must have the same canonical unit). QSET is an abstract type. A working QSET is specified as an expression tree built using a combination of operator (QSI, QSD, QSU, QSP) and component types (QSC, QSS and IVL; and, for TS, PIVL and EIVL). QSETs SHALL not contain null or nullFlavored values as members of the set. |
QSET_INT | r2 | Abstract; specializes ANY Parameter: T : QTY An unordered set of distinct values which are quantities. Any ordered type can be the basis of an QSET; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the QSET must be elements of a totally ordered subset of the partially ordered datatype (for example, PQ is only ordered when the units are consistent. Every value in a QSET(PQ) must have the same canonical unit). QSET is an abstract type. A working QSET is specified as an expression tree built using a combination of operator (QSI, QSD, QSU, QSP) and component types (QSC, QSS and IVL; and, for TS, PIVL and EIVL). QSETs SHALL not contain null or nullFlavored values as members of the set. |
QSET_MO | r2 | Abstract; specializes ANY Parameter: T : QTY An unordered set of distinct values which are quantities. Any ordered type can be the basis of an QSET; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the QSET must be elements of a totally ordered subset of the partially ordered datatype (for example, PQ is only ordered when the units are consistent. Every value in a QSET(PQ) must have the same canonical unit). QSET is an abstract type. A working QSET is specified as an expression tree built using a combination of operator (QSI, QSD, QSU, QSP) and component types (QSC, QSS and IVL; and, for TS, PIVL and EIVL). QSETs SHALL not contain null or nullFlavored values as members of the set. |
QSET_PQ | r2 | Abstract; specializes ANY Parameter: T : QTY An unordered set of distinct values which are quantities. Any ordered type can be the basis of an QSET; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the QSET must be elements of a totally ordered subset of the partially ordered datatype (for example, PQ is only ordered when the units are consistent. Every value in a QSET(PQ) must have the same canonical unit). QSET is an abstract type. A working QSET is specified as an expression tree built using a combination of operator (QSI, QSD, QSU, QSP) and component types (QSC, QSS and IVL; and, for TS, PIVL and EIVL). QSETs SHALL not contain null or nullFlavored values as members of the set. |
QSET_QTY | r2 | Abstract; specializes ANY Parameter: T : QTY An unordered set of distinct values which are quantities. Any ordered type can be the basis of an QSET; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the QSET must be elements of a totally ordered subset of the partially ordered datatype (for example, PQ is only ordered when the units are consistent. Every value in a QSET(PQ) must have the same canonical unit). QSET is an abstract type. A working QSET is specified as an expression tree built using a combination of operator (QSI, QSD, QSU, QSP) and component types (QSC, QSS and IVL; and, for TS, PIVL and EIVL). QSETs SHALL not contain null or nullFlavored values as members of the set. |
QSET_REAL | r2 | Abstract; specializes ANY Parameter: T : QTY An unordered set of distinct values which are quantities. Any ordered type can be the basis of an QSET; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the QSET must be elements of a totally ordered subset of the partially ordered datatype (for example, PQ is only ordered when the units are consistent. Every value in a QSET(PQ) must have the same canonical unit). QSET is an abstract type. A working QSET is specified as an expression tree built using a combination of operator (QSI, QSD, QSU, QSP) and component types (QSC, QSS and IVL; and, for TS, PIVL and EIVL). QSETs SHALL not contain null or nullFlavored values as members of the set. |
QSET_TS | r2 | Abstract; specializes ANY Parameter: T : QTY An unordered set of distinct values which are quantities. Any ordered type can be the basis of an QSET; it does not matter whether the base type is discrete or continuous. If the base datatype is only partially ordered, all elements of the QSET must be elements of a totally ordered subset of the partially ordered datatype (for example, PQ is only ordered when the units are consistent. Every value in a QSET(PQ) must have the same canonical unit). QSET is an abstract type. A working QSET is specified as an expression tree built using a combination of operator (QSI, QSD, QSU, QSP) and component types (QSC, QSS and IVL; and, for TS, PIVL and EIVL). QSETs SHALL not contain null or nullFlavored values as members of the set. |
QSI_PQ | r2 | Specializes QSET Specifies a QSET as an intersection of other sets. |
QSI_TS | r2 | Specializes QSET Specifies a QSET as an intersection of other sets. |
QSP_PQ | r2 | Specializes QSET Specifies a QSET as the periodic hull between two sets. A periodic hull may be generated by comparing two sets that interleave. For QSET values A and B to interleave, the occurrence intervals of both groups can be arranged in pairs of corresponding occurrence intervals. It must further hold that for all corresponding occurrence intervals a ⊆ A and b ⊆ B, a starts before b starts (or at the same time) and b ends after a ends (or at the same time). The interleaves-relation holds when two schedules have the same average frequency, and when the second schedule never “outpaces” the first schedule. That is, no occurrence interval in the second schedule may start before its corresponding occurrence interval in the first schedule. |
QSP_TS | r2 | Specializes QSET Specifies a QSET as the periodic hull between two sets. A periodic hull may be generated by comparing two sets that interleave. For QSET values A and B to interleave, the occurrence intervals of both groups can be arranged in pairs of corresponding occurrence intervals. It must further hold that for all corresponding occurrence intervals a ⊆ A and b ⊆ B, a starts before b starts (or at the same time) and b ends after a ends (or at the same time). The interleaves-relation holds when two schedules have the same average frequency, and when the second schedule never “outpaces” the first schedule. That is, no occurrence interval in the second schedule may start before its corresponding occurrence interval in the first schedule. |
QSS_PQ | r2 | Specializes QSET Specifies a QSET as an enumeration of simple values. This is a shortcut form for specifying the same values as singleton intervals |
QSS_TS | r2 | Specializes QSET Specifies a QSET as an enumeration of simple values. This is a shortcut form for specifying the same values as singleton intervals |
QSU_PQ | r2 | Specifies a QSET as a union of other sets |
QSU_TS | r2 | Specifies a QSET as a union of other sets |
QTY | r2 | The quantity datatype is an abstract generalization for all datatypes whose domain values has an order relation (less-or-equal) and where difference is defined in all of the datatype’’s totally ordered value subsets. The quantity type abstraction is needed in defining certain other types, such as the interval, and probability distributions. |
REAL | r2 | Fractional numbers. Typically used whenever quantities are measured, estimated, or computed from other real numbers. The typical representation is decimal, where the number of significant decimal digits is known as the precision. |
RTO | r2 | A quantity constructed as the quotient of a numerator quantity divided by a denominator quantity. Common factors in the numerator and denominator are not automatically cancelled out. The RTO datatype supports titers (e.g., “1:128”) and other quantities produced by laboratories that truly represent ratios. Ratios are not simply “structured numerics”, particularly blood pressure measurements (e.g. “120/60”) are not ratios. Notes: 1. Ratios are different from rational numbers, i.e., in ratios common factors in the numerator and denominator never cancel out. A ratio of two real or integer numbers is not automatically reduced to a real number. This datatype is not defined to generally represent rational numbers. It is used only if common factors in numerator and denominator are not supposed to cancel out. This is only rarely the case. For observation values, ratios occur almost exclusively with titers. In most other cases, REAL should be used instead of the RTO. 2. Since many implementation technologies expect generics to be collections, or only have one parameter, RTO is not implemented as a generic in this specification. Constraints at the point where the RTO is used will define which form of QTY are used. |
SC | r2 | A character string that optionally may have a code attached. The text must always be present if a code is present. The code is often a local code. SC is used in cases where coding is exceptional (e.g., user error messages are essentially text messages, and the text message is the important content. However sometimes messages come from a catalog of prepared messages, which SC allows to reference). Any non-null SC value MAY have a code, however, a code SHALL NOT be given without the text. The similarities and differences between SC and CD are discussed in Section 7.5.1.2, CD and SC |
SD.TEXT | r2 | A definition of structured text that can be used in healthcare. The structured text is based on an XHTML-like arrangement that ensures the text is properly marked up with semantics, and provides a common base line for implementation in healthcare. The type SD.TEXT is also known as StrucDoc.Text (for legacy reasons). |
SD.TITLE | r2 | A definition of structured title that can be used in healthcare. The structured text is based on the Structured Text definition, but only a narrow set of features can be used, consistent with a title rather than a general document. The type SD.TITLE is also known as StrucDoc.Title (for legacy reasons). |
SLIST_INT | r2 | A sequence of sampled values scaled and translated from a list of integer values. Used to specify sampled biosignals. |
SLIST_PQ | r2 | A sequence of sampled values scaled and translated from a list of integer values. Used to specify sampled biosignals. |
SLIST_REAL | r2 | A sequence of sampled values scaled and translated from a list of integer values. Used to specify sampled biosignals. |
SLIST_TS | r2 | A sequence of sampled values scaled and translated from a list of integer values. Used to specify sampled biosignals. |
ST | r2 | The character string datatype stands for text data, primarily intended for machine processing (e.g., sorting, querying, indexing, etc.) or direct display. Used for names, symbols, presentation and formal expressions. A ST SHALL have at least one character or else have a nullFlavor |
TEL | r2 | A locatable resource that is identified by a URI, such as a web page, a telephone number (voice, fax or some other resource mediated by telecommunication equipment), an e-mail address, or any other locatable resource that can be specified by a URL. The address is specified as a Universal Resource Locator (URL) qualified by time specification and use codes that help in deciding which address to use for a given time and purpose. The value attribute is constrained to be a uniform resource locator specified according to IETF RFCs 1738 and 2806 when used in this datatype. Note: The intent of this datatype is to be a locator, not an identifier; this datatype is used to refer to a locatable resource using a URL, and knowing the URL allows one to locate the object. However some use cases have arisen where a URI is used to refer to a locatable resource. Though this datatype allows for URIs to be used, the resource identified SHOULD always be locatable. A common use of locatable URI’’s is to refer to SOAP attachments. |
TS | r2 | A quantity specifying a point on the axis of natural time. A point in time is most often represented as a calendar expression. |
UVP_AD | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_BL | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_CD | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_CO | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_CS | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_ED | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_EN | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_II | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_INT | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_MO | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_PQ | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_REAL | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_RTO | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_SC | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_ST | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_TEL | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
UVP_TS | r2 | Specializes ANY Parameter: T : ANY A generic datatype extension used to specify a probability expressing the information producer’’s belief that the given value holds. |
XP | r2 | A part of a name or address. Each part is a character string that may be coded, and that also may have a nullFlavor. The string content must always be provided whether a code is provided or not. |
5.2.8. Casts from Complex Types to simple types¶
This table shows for each cast from a complex type to a simple type, whether the cast can be used implicitly (yes) or whether an explicit cast is needed (no), and the function that implements the cast.
5.2.8.1. Complex Type R1 casts¶
Source | Schema | Target | Implicit | Function |
---|---|---|---|---|
BL | r1 | bl | yes | bl2bl |
BXIT_CD | r1 | cs | yes | cd2cv |
BXIT_CD | r1 | cv | yes | cd2cv |
BXIT_CD | r1 | set | yes | cd2set_cs |
CD | r1 | cv | yes | cd2cv |
CD | r1 | set | yes | cd2set_cs |
CE | r1 | cs | yes | cd2cv |
CE | r1 | cv | yes | cd2cv |
CE | r1 | set | yes | cd2set_cs |
CO | r1 | cs | yes | cd2cv |
CO | r1 | cv | yes | cd2cv |
CO | r1 | set | yes | cd2set_cs |
CS | r1 | cs | yes | cd2cv |
CS | r1 | cv | yes | cd2cv |
CS | r1 | set | yes | cd2set_cs |
CV | r1 | cs | yes | cd2cv |
CV | r1 | cv | yes | cd2cv |
CV | r1 | set | yes | cd2set_cs |
HXIT_CE | r1 | cs | yes | cd2cv |
HXIT_CE | r1 | cv | yes | cd2cv |
HXIT_CE | r1 | set | yes | cd2set_cs |
INT | r1 | int4 | yes | int2int |
INT | r1 | numeric | yes | int2numeric |
INT | r1 | text | yes | int2text |
IVL_INT | r1 | ivl_pq | yes | ivl_int2ivl_int |
IVL_PQ | r1 | ivl_pq | yes | ivl_pq2ivl_pq |
IVL_REAL | r1 | ivl_pq | yes | ivl_real2ivl_real |
IVL_TS | r1 | ivl_ts | yes | ivl_ts2ivl_ts |
PQ | r1 | pq | yes | pq2pq |
PQR | r1 | cs | yes | cd2cv |
PQR | r1 | cv | yes | cd2cv |
PQR | r1 | set | yes | cd2set_cs |
ST | r1 | text | yes | st2text |
SXCM_CD | r1 | cs | yes | cd2cv |
SXCM_CD | r1 | cv | yes | cd2cv |
SXCM_CD | r1 | set | yes | cd2set_cs |
TS | r1 | ivl_ts | yes | ts2ivl_ts |
TS | r1 | ts | yes | ts2ts |
This table shows for each cast from a complex type to a simple type, whether the cast can be used implicitly (yes) or whether an explicit cast is needed (no), and the function that implements the cast.
5.2.8.2. Complex Type R2 casts¶
Source | Schema | Target | Implicit | Function |
---|---|---|---|---|
BL | r2 | bl | yes | bl2bl |
CD | r2 | cv | yes | cd2cv |
CD | r2 | set | yes | cd2set_cs |
CS | r2 | cs | yes | cd2cv |
CS | r2 | cv | yes | cd2cv |
CS | r2 | set | yes | cd2set_cs |
INT | r2 | int4 | yes | int2int |
INT | r2 | numeric | yes | int2numeric |
INT | r2 | text | yes | int2text |
IVL_INT | r2 | ivl_pq | yes | ivl_int2ivl_pq |
IVL_PQ | r2 | ivl_pq | yes | ivl_pq2ivl_pq |
IVL_REAL | r2 | ivl_pq | yes | ivl_real2ivl_pq |
IVL_TS | r2 | ivl_ts | yes | ivl_ts2ivl_ts |
PQ | r2 | pq | yes | pq2pq |
PQR | r2 | cs | yes | cd2cv |
PQR | r2 | cv | yes | cd2cv |
PQR | r2 | set | yes | cd2set_cs |
ST | r2 | text | yes | st2text |
TS | r2 | ivl_ts | yes | ts2ivl_ts |
TS | r2 | ts | yes | ts2ts |
5.3. Simple Types¶
The name ‘simple’ type originates from the XML ITS datatype XSD for R1 types, that makes the distiction between XSD simple and complex types. Simple types are atomic, scalar types.
5.3.1. Simple Healthcare Datatypes¶
Mnemonic | Name | Description |
---|---|---|
any | DataValue | abstract most generic type |
bl | Boolean | binary value for use in boolean logic |
bn | Boolean (NonNull) | a boolean constrained so that it is not null |
cv | Coded Value | a reference to a concept defined in a code system |
ii | Instance Identifier | a unique identifier that guarantees the global uniqueness of the instance identifier |
ivl_pq | Interval of Physical Quantities | a set of consecutive values of physical quantities |
ivl_ts | Interval of Point in Time | a set of consecutive values of time-stamps |
pq | Physical Quantity | a dimensioned quantity with a value and a unit |
pq_time | Length of Time | a flavor of PQ that is constrained to the time dimension |
qset_ts | Contineous set of Point in Time | a set of intervals in time |
rto | Ratio | a quantity constructed as the quotient of a numerator quantity divided by a denominator quantity. |
ts | Point in Time | a quantity specifying a point in time on the axis of time |
ts_date | Date | a flavor of TS that only allows a maximum precision of days and no timezones |
ts_date_full | FullDate | a flavor of TS that only allows a precision of days and no timezones |
ts_datetime | DateTime | a flavor of TS that only allows a maximum precision of seconds |
ts_datetime_f ull | FullDateTime | a flavor of TS that only allows a precision of seconds |
ts_birth | Birth Date | a flavor of TS that constrains timezone and precision to only allow birth years, dates or times |
5.3.2. DataValue (‘any’)¶
The any
datatype defines the basic properties of every data
value. This is conceptually an abstract type, meaning that no proper value
can be just a data value without belonging to any concrete type. Every
concrete type is a specialization of this general abstract datatype.
lake=# CREATE TABLE testany (a hl7.any);
CREATE TABLE
lake=# INSERT INTO testany VALUES ('10 ml');
ERROR: cannot read 'any' value
LINE 1: INSERT INTO testany VALUES ('10 ml');
^
HINT: 'any' value can only be created by casting another contrete type.
lake=# INSERT INTO testany VALUES ('10 ml'::pq);
INSERT 0 1
5.3.2.1. NullFlavors¶
A data type may have an exceptional value rather than a proper value, either because the information does not exist, is not known or available, or cannot be expressed in the allowed value domain. In this case, the NullFlavor expresses in what way and why proper information is missing.
The NullFlavors extend the domains of all Healthcare Datatypes (“domain”
in this sense means the set of all possible values for the data type, not
“domain” in the more restricted sense used for coded data types). So this
is true not only for coded data types with constrained vocabulary domains,
but for non-coded value domains as well, e.g. integers, temporal
intervals, etc. As a general domain extension of all Healthcare Datatypes,
the null flavors also extend the literal form of those data types that
have a literal form. In any literal form, the literal
NullFlavor.X
signals that the data type has the assigned
NullFlavor, where X
is the code, such as NA
.
Some of the null flavors are not generally applicable to all data
types. The NullFlavors NINF,PINF
,
DER,QS
, and TRC
can only be used
in associated with QTY
types. The NullFlavor UNC
can only be used with any type that has an
originalText
, and when UNC
is used the
originalText
property must be populated. The NullFlavor
DER
may only be used with the EXPR
type, and an
expression must be provided.
SELECT 'NullFlavor.TRC'::bl;
ERROR: The NullFlavor 'NullFlavor.TRC' can only be used in association with QTY types
5.3.2.1.1. NullFlavors¶
Leve l | Code | Name | Description |
---|---|---|---|
1 | NI | no information | The value is exceptional (missing, omitted, incomplete, improper). No information as to the reason for being an exceptional value is provided. This is the most general exceptional value. It is also the default exceptional value. |
2 | INV | invalid | The value as represented in the instance is not a member of the set of permitted data values in the constrained value domain of a variable. |
3 | OTH | other | The actual value is not a member of the set of permitted data values in the constrained value domain of a variable. (e.g., concept not provided by required code system). |
4 | NINF | negative infinity | Negative infinity of numbers. |
4 | PINF | positive infinity | Positive infinity of numbers. |
3 | UNC | unencoded | No attempt has been made to encode the information correctly but the raw source information is represented (usually in originalText). |
3 | DER | derived | An actual value may exist, but it must be derived from the provided information (usually an expression is provided directly). |
2 | UNK | unknown | A proper value is applicable, but not known. |
3 | ASKU | asked but unknown | Information was sought but not found (e.g., patient was asked but didn’t know) |
4 | NAV | temporarily unavailable | Information is not available at this time but it is expected that it will be available later. |
3 | QS | sufficient quantity | The specific quantity is not known, but is known to be non-zero and is not specified because it makes up the bulk of the material. e.g. ‘Add 10mg of ingredient X, 50mg of ingredient Y, and sufficient quantity of water to 100mL.’ The null flavor would be used to express the quantity of water. |
3 | NASK | not asked | This information has not been sought (e.g., patient was not asked) |
3 | TRC | trace | The content is greater than zero, but too small to be quantified. |
2 | MSK | masked | There is information on this item available but it has not been provided by the sender due to security, privacy or other reasons. There may be an alternate mechanism for gaining access to this information. Note: using this null flavor does provide information that may be a breach of confidentiality, even though no detail data is provided. Its primary purpose is for those circumstances where it is necessary to inform the receiver that the information does exist without providing any detail. |
2 | NA | not applicable | No proper value is applicable in this context (e.g., last menstrual period for a male). |
5.3.2.2. Interaction between database NULL and NullFlavors¶
The NullFlavors are conceptually similar to the database servers regular
NULL values: both implement Codd’s three-valued semantics. Without
NOT NULL
constraints, a data value can be both kinds of
null. The following must be kept in mind:
The regular NOT NULL
constraint does not apply to
NullFlavors; it is possible to store NullFlavor.NI
in an
table column that has a NOT NULL
constraint.
CREATE TABLE testnull (a pq NOT NULL);
INSERT INTO testnull VALUES (NULL);
INSERT INTO testnull VALUES ('NullFlavor.NI');
CREATE TABLE
ERROR: null value in column "a" violates not-null constraint
INSERT 0 1
Conversely, the Healthcare Datatype nonNull
constraint on an
Healthcare Datatype such as bn
, prohibits the use of a
NullFlavor, but it does not prohibit the regular NULL
value.
CREATE TABLE testbn (a bn);
INSERT INTO testbn VALUES ('NullFlavor.NI');
INSERT INTO testbn VALUES (NULL);
CREATE TABLE
ERROR: NullFlavor not allowed: "NullFlavor.NI"
INSERT 0 1
The regular operators IS NULL
and NOTNULL
operate on the regular NULL value only, not the NullFlavor. To query
if a datavalue has a NullFlavor, use the isnull
function.
SELECT 'NullFlavor.NI'::pq IS NULL AS regular;
SELECT isnull('NullFlavor.NI'::pq) AS hl7;
regular
---------
f
(1 row)
hl7
------
true
(1 row)
5.3.2.3. Functions on ‘any’ (simple type)¶
The functions defined on any
are implemented for every
Healthcare Datatype.
Some interpretation of the semantics of the values may be required to determine the result of the function. For example, the type pq has the two semantic properties (1) a real number and (2) a coded unit of measure. The equality test must account for the fact that, e.g., 1 meter equals 100 centimeters; independent equality of the two semantic properties is too strong a criterion for the equality test. Therefore, physical quantity overrides the equality definition.
The requirement for understanding the meaning of the data applies to
NullFlavors as well. Under certain circumstances the result of an
operation where NullFlavors are concerned may be null or not null, and the
result might change when the datatype changes. For instance, the result of
equal(UNK, ASKU)
is UNK
when the datatype is
bl
, and NI
for pq
.
Consult NullFlavors and the functions of the specific data types for more information.
5.3.2.3.1. Functions on ‘any’ (simple type)¶
Function | Args | Return s | Description |
---|---|---|---|
nonnull | any | bn | A predicate indicating that a property has a value, i.e. is a non-null (“non-exceptional”) value of the data type. |
isnull | any | bn | A predicate indicating that that a value is an exceptional value, or a null-value. A null value means that the information does not exist, is not available, or cannot be expressed in the data type’s normal value set. Every data element has either a proper value or it is considered NULL. If (and only if) it is NULL, the NullFlavor provides more detail as to in what way or why no proper value is supplied. |
isnull | any,te xt | bool | A predicate indicating that operand 1 has an
exceptional value of the NullFlavor specified
by operand 2, e.g.
isnull('NullFlavor.NASK'::pq,'NASK') is
true. To select datavalues having a certain
NullFlavor, you cannot use equality between
NullFlavors, since equality on two NullFlavors
returns a NullFlavor and not true, even on two
datatypes having the same NullFlavor. Instead,
use the isnull(x,text) predicate. |
notapplicabl e | any | bn [1] | A predicate indicating that this exceptional value is of NullFlavor not-applicable (NA), i.e., that a proper value is not meaningful in the given context. |
unknown | any | bn | A predicate indicating that this exceptional value is of NullFlavor unknown (UNK). |
other | any | bn | A predicate indicating that this exceptional value is of NullFlavor other (OTH), i.e., that the required value domain does not contain the appropriate value. |
equal | any | bl | An equivalence relation (reflexive, symmetric, and transitive) between any two data values. How equality is determined is defined for each concrete data type. |
identical | any | bl | Identity comparison is a reflexive, symmetric, and transitive relation between any two data values. Any values can be identical, whether or not they are null or contain properties with null values. Two data values are identical when all their compositional properties are equal. |
nullflavor | any | cv | Returns the nullflavor code if a datatype is a nullflavor, database NULL otherwise. |
5.3.2.3.2. Functions defined on any
can be used on all simple types¶
SELECT NullFlavor('NullFlavor.UNK'::pq);
isnull
--------
true
SELECT unknown('NullFlavor.UNK'::pq);
nullflavor
------------------------------
UNK:2.16.840.1.113883.5.1008
(1 row)
SELECT isnull('NullFlavor.INV'::bl);
unknown
---------
true
5.3.3. Boolean (‘bl’)¶
A binary value for use in boolean logic. A bl
value can be
either true or false, or, as any other concrete datatype, may have a
NullFlavor. The functions and operators define for bl
are:
operators:
5.3.3.1. Functions on ‘bl’¶
Operat or | Function | Args | Return s | Description |
---|---|---|---|---|
! | not (negation) | bl | bl | A unary function that negates the truth value; true becomes false and false becomes true. The negation of a NullFlavor returns the same NullFlavor. |
& | and (conjunction ) | bl,bl | bl | The conjunction of the first operand (or conjunct) and the second is true, if and only if both operands are true. |
| | or (disjunction ) | bl,bl | bl | The disjunction of the first operand (or disjunct) and the second is true, if and only one of the operands is true. |
^ | xor (exclusive disjunction) | bl,bl | bl | The exclusive or of the first operand and
the second is true if and only if one of
the operands is true and the other is
false. In other words, p^q is true if
and only if ! (p=q) . The exclusive or
can be defined in terms of and and
or . |
= | equal (equality) | bl,bl | bl | The equality of two operands is true when
both operands are non-null and have the
same value. This is the opposite of the
exclusive or. In other words, p=q if
and only if !(p^q) . |
-> | implies (implication ) | bl,bl | bl | The logical implication of the first
operand (the antecedent) and the second
(the consequent) is true, if and only if
the consequent is true when the
antecendent is true. The implication is
defined in terms of and and or as
follows p->q if and only if
(!p)|q . |
5.3.3.2. Three-valued logic on ‘bl’¶
With any data value potentially having a NullFlavor, the two-valued logic is effectively extended to a three-valued logic. as shown in the following truth tables:
5.3.3.2.1. ‘bl’ negation truth table¶
NOT | |
---|---|
true | false |
false | true |
NullFlavor.X | NullFlavor.X |
5.3.3.2.2. ‘bl’ conjunction truth table¶
AND | true |
---|---|
true | true |
false | false |
NullFlavor.Y | NullFlavor.Y |
5.3.3.2.3. BL disjunction truth table¶
OR | true |
---|---|
true | true |
false | true |
NullFlavor.Y | false |
The truth tables of the other binary operators on BL are omitted, since
they can be expressed in terms of and,or
and
not
.
Where a boolean operation is performed upon 2 data types with different NullFlavors X and Y, the NullFlavor Z of the result is the first common ancestor of the 2 different NullFlavors, though conformant applications may also create a result that is any common ancestor.
SELECT 'NullFlavor.INV'::bl & 'NullFlavor.UNK'::bl AS and;
and
---------------
NullFlavor.NI
5.3.4. Boolean (NonNull) ‘bn’¶
bn
constrains the type bl
so that it is not
null. This is defined for use within the data types specification where it
is not appropriate for a null value to be used.
bl
allows a NullFlavor:
SELECT 'NullFlavor.NI'::bl;
bl
---------------
NullFlavor.NI
(1 row)
Whereas bn
does not:
SELECT 'NullFlavor.NI'::bn;
ERROR: NullFlavor not allowed: "NullFlavor.NI"
5.3.5. Interaction between bl, bn and boolean¶
bl,bn
and the
regular bool
datatypes can be substituted for one
another, as long as there is no violation of constraints.
SELECT true, -- boolean (true without quotes)
'true'::bl, -- normal bl string literal input
true::bl, -- boolean can be cast to bl
(1::bool)::bl; -- another cast of boolean to bl
bool | bl | bl | bl
------+------+------+------
t | true | true | true
(1 row)
5.3.6. Coded Value (cv)¶
A cv
is a reference to a concept defined in a code system.
5.3.6.1. Compositional properties of ‘cv’¶
Name | Type | Description |
---|---|---|
code | text | The plain code symbol defined by the code system, or an expression in a syntax defined by the code system which describes the concept. |
codesystem | uid | The code system that defines the code. |
codesystemvers ion | text | If applicable, the version of the codesystem that
applied at the time of input of the cv
datavalue. |
valueset | uid | The valueset that applied when the data value was instantiated. |
valuesetversio n | text | The version of the value set that applied when the data value was instantiated. datavalue was created. |
originaltext | text | The text as seen and/or selected by the user who entered the data. |
5.3.6.2. Catalog based vocabulary support¶
The cv
datatype makes use of a number of catalog
relations. Installing a codesystem describes how to populate
these catalog files with vocabulary contents. Besides HL7v3s codesystems,
MGRID also supplies SQL scripts to load support other codesystems, such as
SNOMED-CT and LOINC.
5.3.6.3. Constructor methods for ‘cv’¶
The string literal format of cv
is
code:codesystemoid<@codesystemversion><:valuesetoid<@valuesetversion>><\|originaltext>;
. For instance
EVN:2.16.840.1.113883.5.1001
is the reference to the
event concept in the ActMood
codesystem:
SELECT 'EVN:2.16.840.1.113883.5.1001'::cv);
cv
------------------------------
EVN:2.16.840.1.113883.5.1001
(1 row)
As an alternative to enter code codesystem pairs, a convenient way to
input coded values is by constraining cv
, using a type modifier,
to a particular conceptdomain:
SELECT 'EVN'::cv('ActMood');
cv
---------------------------------------------------------------------------------
EVN:2.16.840.1.113883.5.1001@2009-10-20:2.16.840.1.113883.1.11.10196@2009-10-20
(1 row)
5.3.6.4. Code expression syntax check support¶
For all HL7v3 codesystems [2], the
cv
datatype checks if the code is present in the codesystem.
For the SNOMED-CT codesystem 2.16.840.1.113883.6.96
,
input is also checked for correct syntax.
When a code is not found in a codesystem of which the complete extent is
known and is loaded as vocabulary module, a WARNING is returned. It is
possible to configure the cv
input mode to raise an ERROR
instead. Set hdl.concept_input_mode
to enforcing
to raise errors instead of warnings. For
data warehouse loading, permissive might be useful in data loading for
later cleaning. For EHR use cases, enforcing is a more appropriate
setting. See also WARNING: code <code> not found in valueset(s) bound (no exceptions) to conceptdomain <name>.
5.3.6.5. Functions on ‘cv’¶
See Working with coded values for examples how to use these functions.
Opera tor | Function | Argum ents | Retur ns | Description |
---|---|---|---|---|
# | code | cv | text | Returns the plain code symbol defined by the code system, or an expression in a syntax defined by the code system which describes the concept. |
## | codesystem | cv | text | Returns the OID of the codesystem. |
### | valueset | cv | text | Returns the OID of the valueset that
applied when the cv datavalue
was created. |
? | displayname | cv | text | Returns the name, title or representation for the code or expression as it exists in the code system. |
?? | codesystemname | cv | text | Returns the name of the codesystem
of a cv> . |
codesystemname | text | text | Returns the name of the codesystem given the OID of the codesystem. | |
??? | valuesetname | cv | text | Returns the name of the valueset
that applied when the cv
datavalue was created. |
@@ | codesystemversion | cv | text | Returns the version of the
codesystem of the cv . |
@@@ | valuesetversion | cv | text | Returns the version of the valueset
that applied when the cv
datavalue was created. |
= | equal | cv,cv | bl | Returns true if the codes and codesystems of both operands are equal. The code is compared case sensitive. |
<> | notequal | cv,cv | bl | Returns true if the codes and codesystems of both operands are different. |
<< | implies | cv,cv | bl | Returns true if the first operand is a specialization of the second operand. This function is only implemented for the HL7v3 managed codesystems and SNOMED-CT. |
>> | cv,cv | bl | The converse of the implies operator. | |
codesystem | text | set | Returns the codes of the HL7v3 codesystem named by the first operand. | |
codesystem | text, text | set | This function returns the codes of the HL7v3 codesystem named by the first operand, substring matching the second operand. | |
ancestors | cv | text | returns the ancestors of a coded value, as text | |
descendants | cv | text | returns the descendants of a coded value, as text | |
valueset | text | set | Returns the codes of the valueset named by the first operand. | |
valueset | text, text | set | This function returns the codes of the valueset named by the first operand, substring matching the second operand. | |
valueset | text, cv | set | This function returns the codes in the valueset named by the first operand, where the code is a specialization of the second operand. | |
conceptdomain | text | set | Returns the codes of the conceptdomain named by the first operand. | |
conceptdomain | text, text | set | This function returns the codes of the conceptdomain named by the first operand, substring matching the second operand. | |
conceptdomain | text, cv | set | This function returns the codes in the conceptdomain named by the first operand, where the code is a specialization of the second operand. |
5.3.6.6. Indexing¶
The cv
datatype supports two kinds of indexes, refer to
section Using indexes for details.
The guidelines of NullFlavor Handling also apply here. It is
not endorsed to use functions, such as implies
in
WHERE clauses; instead the operator form should be used. The operator for implies is <<
:
SELECT * FROM examplecv WHERE a << 'OBS'::cv('ActClass');
a
------------------------------------------------------------------------------
GEN:2.16.840.1.113883.5.6@2009-10-20:2.16.840.1.113883.1.11.11527@2009-10-20
OBS:2.16.840.1.113883.5.6@2009-10-20:2.16.840.1.113883.1.11.11527@2009-10-20
(2 rows)
5.3.7. Instance Identifier (ii)¶
An identifier that uniquely identifies a thing or object. Examples are object identifier for HL7v3 RIM objects, medical record number, order id, service catalog item id, Vehicle Identification Number (VIN), etc.
5.3.7.1. Compositional properties of ‘ii’¶
Name | Type | Description |
---|---|---|
root | uid | A unique identifier that guarantees the global
uniqueness of the instance identifier. The root
alone may be the entire instance identifier.
Please refer to the uuid datatype description
of the database server and the contrib module
contrib/uuid-ossp for information on
generating uuid values. |
extension | text | The extension is a character string that is unique in the namespace designated by the root. If a non-NULL extension exists, the root specifies a namespace (sometimes called “assigning authority” or “identifier type”). |
Not supported are the scope
and
reliability
properties defined by the HL7v3
standard.
5.3.7.2. Constructor methods¶
The string literal form of ii
is
<root>:<extension>
, for a nonnull
extension, and <root>
if there is no
extension. Here follow some examples with the ii
data type:
CREATE TABLE exampleii (a ii);
INSERT INTO exampleii VALUES
('2.16.840.1.113883.4.1.123121234'),
('2.16.840.1.113883.4.1:alphanumeric');
With the contrib module contrib/uuid-ossp
it is easy to generate
uuid’s:
INSERT INTO exampleii VALUES
(uuid_generate_v1()),
('a982cc82-3e25-11de-a7a5-6bc8c3687cf5:anotherextension');
SELECT * FROM exampleii;
a
-------------------------------------------------------
38e0055e-3e3d-11de-ba73-f72d0933defc
a982cc82-3e25-11de-a7a5-6bc8c3687cf5:anotherextension
(2 rows)
5.3.7.3. Functions on ‘ii’¶
Opera tor | Function | Argum ents | Retur ns | Description |
---|---|---|---|---|
# | root | ii | text | Returns the root of the identifier as text. |
## | extension | ii | text | Returns the extension of the identifier as text. |
? | identifiername | ii | text | Returns the name of the codesystem if the identifier root is an OID. Returns the string UUID if the identifier root is an UUID. |
= | equal | ii, ii | bl | Returns true if the root and the extension match, false otherwise. Extensions are matched case sensitive. |
<> | notequal | ii, ii | bl | Returns true if the root and the extension are different, false otherwise. Extensions are matched case sensitive. |
5.3.7.4. Indexing¶
The ii
datatype supports two kinds of indexes, refer to section Using indexes for details.
The guidelines of NullFlavor Handling also apply here. It is
not endorsed to use functions, such as equal
in WHERE clauses; instead
the operator form =
should be used:
SELECT * FROM exampleii WHERE a = '2.16.840.1.113883.4.1:123121234'::ii;
a
---------------------------------
2.16.840.1.113883.4.1:123121234
(1 row)
5.3.8. Ratio (rto)¶
A rational number (ratio) is the set of all numbers that can be expressed as the quotient of a numerator quantity divided by a denominator quantity.
5.3.8.1. Literal form¶
The literal form of rto
has the form
numerator
:denominator
. The numerator
is the quantity that is being divided and has a default value of the
integer number 1. The denominator is the quantity that divides the
numerator. The value of the denominator shall not be
zero. Its default value is the integer number 1. Example:
SELECT '1:64'::rto;
rto
------
1:64
(1 row)
5.3.8.2. Functions on ‘rto’¶
Opera tor | Function | Arguments | Retur ns | Description |
---|---|---|---|---|
demotionnumer
ic |
rto | numer ic | Demotes rto to a real by
dividing the numerator by the
denominator. |
|
demotionpq |
rto | pq | Demotes rto to a pq by dividing
the numerator by the denominator.
The units are derived by dividing
the numerator unit by the
denominator unit. |
|
= | equal |
rto,rto | bl | Two rto are equal if their real
equivalents are equal. This means
that the rto ‘1:2’ equals ‘2:4’. |
<> | notequal |
rto,rto | bl | Two rto are equal if their real
equivalents are equal. This means
that the rto ‘1:2’ equals ‘2:4’. |
<= | lessorequal |
rto,rto | bl | Returns true if the real value of the first operand is smaller than or equals the second operands real value. |
< | lessthan |
rto,rto | bl | Returns true if the real value of the first operand is smaller than the second operands real value. |
>= | greaterorequa
l |
rto,rto | bl | Returns true if the real value of the first operand is greater than or equals the second operands real value. |
> | greaterthan |
rto,rto | bl | Returns true if the real value of the first operand is greater than the second operands real value. |
The examples that follow below use this example table:
CREATE TABLE ratios (a rto, b rto);
INSERT INTO ratios VALUES ('1:2','2:4'),('1:3','4:7'),('NullFlavor.TRC','-1:8');
SELECT * FROM ratios;
CREATE TABLE
INSERT 0 2
a | b
----------------+------
1:2 | 2:4
1:3 | 4:7
NullFlavor.TRC | -1:8
(3 rows)
Demotionreal and demotionpq:
SELECT demotionnumeric(a), demotionpq(a) FROM ratios;
demotionnumeric | demotionpq
------------------------+------------------------
0.5000 | 0.5
0.33333333333333333333 | 0.33333333333333333333
| NullFlavor.TRC
(3 rows)
Use functions in SELECT lists:
SELECT greaterthan(a,b), isnull(a, 'TRC') FROM ratios;
greaterthan | isnull
-------------+--------
false | f
false | f
true | t
(3 rows)
Use operators in WHERE clauses:
SELECT * FROM ratios WHERE a >= b;
a | b
----------------+------
1:2 | 2:4
NullFlavor.TRC | -1:8
(2 rows)
5.3.9. Physical Quantity (pq)¶
A dimensioned quantity expressing the result of measuring. A physical
quantity is a value and a unit of measure, for instance 10
ml/kg
. The value is the magnitude of the quantity, and is stored
internally as a REAL
(numeric
). The unit is
string that conforms to the *Unified Code for Units
of Measure (UCUM)*. See Unified Codes for Units of Measure for a
complete list of the units.
The units that conform to UCUM are stored in the catalog table
hdl.pg_ucumunit
. An example to query the units is:
SELECT uuname,uudimension,uudescription FROM pg_ucumunit;
uuname | uudimension | uudescription
-------------+-------------------+-------------------------------------------------------------
m | m | length
g | g | mass
s | s | time
rad | rad | plane angle
K | K | temperature
C | C | electric charge
cd | cd | luminous intensity
10* | 1 | the number ten for arbitrary powers
10^ | 1 | the number ten for arbitrary powers
[pi] | 1 | the number pi
% | 10*-2 | percent
[ppth] | 10*-3 | parts per thousand
[ppm] | 10*-6 | parts per million
[ppb] | 10*-9 | parts per billion
[pptr] | 10*-12 | parts per trillion
mol | 10*23 | mole
sr | rad2 | steradian
Hz | s-1 | Hertz
N | kg.m/s2 | Newton
Pa | N/m2 | Pascal
J | N.m | Joule
W | J/s | Watt
A | C/s | Ampère
V | J/C | Volt
F | C/V | Farad
Ohm | V/A | Ohm
S | Ohm-1 | Siemens
Wb | V.s | Weber
Cel | K | degree Celsius
T | Wb/m2 | Tesla
... (rows omitted here)
sph | [pi].sr | spere
[car_m] | g | metric carat
[car_Au] | /24 | carat of gold alloys
[smoot] | [in_i] | Smoot
bit_s | 1 | bit
bit | 1 | bit
By | bit | byte
Bd | /s | baud
(264 rows)
Here are some physical quantities with units conforming to UCUM:
SELECT '10 ml'::pq, '120 mm[Hg]'::pq, '10%'::pq, '80 kg{bodyweight}'::pq, 'NullFlavor.QS ml'::pq;
pq | pq | pq | pq | pq
-------+------------+------+-------------------+------------------
10 ml | 120 mm[Hg] | 10 % | 80 kg{bodyweight} | NullFlavor.QS ml
Since monkeys
does not conform to UCUM, it cannot be used as unit:
SELECT '10 monkeys'::pq;
ERROR: bad ucum representation
DETAIL: unknown unit symbol at or near position 3:"monkeys"
Physical quantities can also be stored in tables with columns of type pq or a generalization of pq:
CREATE TABLE things (a pq, b hl7.any);
INSERT INTO things VALUES ('80 kg{bodyweight}','120 mm[Hg]'::pq);
INSERT INTO things VALUES ('NullFlavor.QS ml','10 %'::pq);
SELECT * FROM things;
CREATE TABLE
INSERT 0 1
INSERT 0 1
a | b
-------------------+------------
80 kg{bodyweight} | 120 mm[Hg]
NullFlavor.QS ml | 10 %
The pq
type and the NullFlavor handling conform to the
Healthcare Datatype specification. It is impossible to create data that
does not conform to the specification. For instance, not all NullFlavors
are allowed in the pq
datatype:
INSERT INTO things VALUES ('NullFlavor.DER',NULL);
ERROR: The NullFlavor 'NullFlavor.DER' can only be used in association with the type EXPR
5.3.9.1. Functions and operators on ‘pq’¶
Opera tor | Function | Arguments | Retur ns | Description |
---|---|---|---|---|
canonical |
pq | pq | Returns the physical quantity expressed in base units. The 7 base units are m (meter), s (second), g (gram), K (Kelvin), cd (candela), C (coulomb), rad (radian). | |
= | equal |
pq,pq | bl | Two physical quantities are equal if
their canonical forms have the same
value and unit. For instance,
1 m and 100 cm are equal.
Returns NullFlavor.NA if operands do
not compare. |
< | lessthan |
pq,pq | bl | Less than on pq values. Returns
Null if the operands do not compare. |
<= | lessorequal |
pq,pq | bl | Less or equal on pq values.
Returns Null if the operands do not
compare. |
>= | greaterorequa
l |
pq,pq | bl | Greater or equal on pq values.
Returns Null if the operands do not
compare. |
> | greaterthan |
pq,pq | bl | Greater than on pq values.
Returns Null if the operands do not
compare. |
/= | compares |
pq,pq | bl | Two physical quantities compare with each other if the units of their canonical forms are equal. |
isone |
pq | bl | A predicate indicating if this value is the number one, i.e., the neutral element of multiplication. There is exactly one physical quantity that has this property and is called the unity. | |
* | times |
pq,real | pq | Scalar multiplication of the value property. |
* | times |
pq,pq | pq | The product of two physical quantities is the product of their values times the product of their units. |
/ | dividedby |
pq,real | pq | Scalar division of the value property. |
/ | dividedby |
pq,pq | pq | The division of one physical quantities (dividend) by another (divisor) is the division of their values times the division of their units. |
! | inverted |
pq | pq | A pq value, which, when
muliplied with another pq value
yields one (the neutral element of
multiplication). Zero (the neutral
element of addition) has no inverse
element. The quotient of two
comparable quantities is comparable
to the unity (the unit 1). |
topq |
real, int, numeric | pq | Make a pq value from a real, int
or numeric with unit 1. |
|
demotion |
pq | real | If a quantity has the unit 1 (one)
it can be converted to a REAL
number. For instance,
demotion('100'::pq) is the
number 100 . |
|
^ | power |
pq,real [3 ]_ | pq | A physical quantity can be raised to an REAL power. |
+ | add |
pq,pq | pq | Two physical quantities that compare each other can be added. |
- | subtract |
pq,pq | pq | Two physical quantities that compare each other can be subtracted. |
unit |
pq | cs | Returns the unit of the quantity. | |
value |
pq | real | Returns the value of the quantity. | |
convert |
pq,text | pq | Converts the first operand to another pq with the unit specified by the second operand. The units must compare. |
Recall from section NullFlavor Handling that functions like
equal
or greaterthan
must be used
in SELECT lists, and in WHERE clauses their corresponding operators = and
>= must be used. It is not endorsed to use functions in WHERE clauses if an
operator exists.
Section Using indexes describes how to create indexes on pq
columns.
5.3.9.2. Aggregates on ‘pq’¶
pq
aggregates can only operate on a set of pq
values that
are comparable to each other. Values are considered in their canonical
representation during the aggregation.
Aggregate | Returns | Description |
---|---|---|
sum | pq | Returns the sum. |
avg | pq | Returns the mean. |
stddev | pq | Returns the population standard deviation. |
stddev_pop | pq | Alias for stddev. |
stddev_samp | pq | Returns the sample standard deviation. |
variance | pq | Returns the population variance. |
var_pop | pq | Alias for variance. |
var_samp | pq | Returns the sample variance. |
An example using a table that only contains measures of length:
CREATE TABLE pqa(v pq);
INSERT INTO pqa VALUES ('1m'), ('2m'), ('3m'), ('4m'), ('5m'), ('6m'), ('7m'), ('8m'), ('9m'), ('10m');
SELECT sum(v), count(v), avg(v), stddev(v), variance(v) FROM pqa;
CREATE TABLE
INSERT 0 10
sum | count | avg | stddev | variance
------+-------+-------+--------------+----------
55 m | 10 | 5.5 m | 2.87228132 m | 8.25 m.m
(1 row)
5.3.9.3. Flavors of ‘pq’¶
The type length of time pq_time
constraints pq
so that is has a unit that describe a period
of time. Valid use of pq_time
include all physical quantities
that compare to the base unit s
(seconds). This type is
used by ivl_ts
for its width and centerwidth forms, described
in Centerwidth form.
SELECT '1 s'::pq_time;
SELECT '24 h'::pq_time;
pq_time
---------
1 s
(1 row)
pq_time
---------
24 h
(1 row)
If a unit does not compare to s
it cannot be used as a unit of pq_time
:
SELECT '1 eV'::pq_time;
ERROR: value for domain pq_time violates check constraint "pq_time_compares_to_s"
Use the following query to show the units that compare to s
SELECT uuname, uudimension, uubasefactor, uudescription
FROM pg_ucumunit
WHERE uubase_s=1 and uubase_m+uubase_g+uubase_rad+uubase_k+uubase_c+uubase_cd=0;
uuname | uudimension | uubasefactor | uudescription
--------+-------------+----------------+----------------------
s | s | 1 | time
min | s | 6e1 | minute
h | min | 3.6e3 | hour
d | h | 8.64e4 | day
a_t | d | 3.1556925216e7 | tropical year
a_j | d | 3.15576e7 | mean Julian year
a_g | d | 3.1556952e7 | mean Gregorian year
a | a_j | 3.15576e7 | year
wk | d | 6.048e5 | week
mo_s | d | 2.551442976e6 | synodal month
mo_j | a_j/12 | 2.6298e6 | mean Julian month
mo_g | a_g/12 | 2.629746e6 | mean Gregorian month
mo | mo_j | 2.6298e6 | month
[S] | 10*-13.s | 1e-13 | Svedberg unit
5.3.10. Point in Time (ts)¶
A quantity specifying a point on the axis of natural time. Internally, a
ts
is very similar to the Postgresql timestamp-with-timezone
datatype. Both are implemented by storing the number of ticks that have
transpired since an (arbitrary) starting date called the epoch. Both also
are aware of timezones and internally store the values as UTC
timestamps.
The difference between a ts
and the
regular PostgreSQL timestamp is
that ts
is always returned with the same precision as it was
input. A regular timestamp is formatted according
to PostgreSQL datastyle settings. Another
difference between PostgreSQL timestamp and
HL7 TS
is that the the HL7 ts
comparison
functions also consider the precision of the operands. This means that two
TS can only be compared if they have the same precision in the integer
part:
SELECT equal('20081217143012'::ts,'20081217'::ts);
equal
---------------
NullFlavor.NA
(1 row)
If two timestamps have the same precision in their integerpart, they can be compared:
SELECT equal('20081217143012.000'::ts,'20081217143012'::ts);
equal
-------
true
(1 row)
5.3.10.1. Interaction between ‘ts’, date and timestamp with time zone¶
The ts
datatype is equally expressive as the native time
timestamp with time zone
datatype. Two casts are provided, so
the standard timestamp and date functions of the database server can be
used:
From
timestamp with timezone
tots
. The result is ats
with maximum precision and timezone information. This is the recommended form to register times of events, since these timestamps can be converted to UTC.SELECT current_timestamp::ts AS timestamp; timestamp ---------------------------- 20081118193313.274941+0100 (1 row)
From
date
tots
. The result is ats
with precision 8 and no timezone. It is not recommended to use this point in time format to register event times, use the full datetime format of timestamp with timezone instead.SELECT current_date::ts AS date; date ---------- 20081118 (1 row)
5.3.10.2. Functions on ‘ts’¶
Oper ator | Function | Args | Returns | Description |
---|---|---|---|---|
promotion | ts | ivl_ts | Promotes a ts to an
interval. |
|
offset | ts | pq | The elapsed time since any constant epoch, measured as a physical quantity in the dimension of time (i.e., comparable to one second). | |
= | equal | ts,ts | bl | Equality function for ts .
Returns Null if the precision
of the operands differ. |
< | lessthan | ts,ts | bl | Less than on ts values.
Returns Null if the precision
of the operands differ. |
<= | lessorequal | ts,ts | bl | Less or equal on ts
values. Returns Null if the
precision of the operands
differ. |
>= | greateror equal | ts,ts | bl | Greater or equal on ts
values. Returns Null if the
precision of the operands
differ. |
>> | greaterthan | ts,ts | bl | Greater than on ts
values. Returns Null if the
precision of the operands
differ. |
calendar | ts | cs | A code specifying the
calendar used in the literal
representation of this
ts . MGRID HDL currently
only implements the Gregorian
calendar, hence this function
always returns GREG . |
|
precision | ts | int | The number of significant digits of the calendar expression representation. | |
timezone | ts | pq | The difference between the
local time in that time zone
and the Universal Coordinated
Time (UTC, formerly called
Greenwich Mean Time, GMT).
When timezone is NULL,
“local time” is assumed.
However, “local time” is
always local to some place,
and without knowledge of that
place, the time zone is
unknown. Hence, a local time
cannot be converted to UTC. |
|
+ | plus | ts, pq_time | ts | A ts plus an elapsed time
is a ts . |
- | minus | ts, pq_time | ts | A ts minus an elapsed
time is a ts . |
- | minus | ts, ts | pq | The difference between two
ts values is an elapsed
time. |
timestamptz 2ts | timestamp with timezone | ts | Casts PostgreSQL internal
timestamp with timezone
datatype to the HL7 ts
type. |
|
ts2timestamp tz | ts | timestamp with timezone | Casts HL7 ts to
PostgreSQL internal
timestamp with timezone . |
|
date2ts | date | ts | Casts PostgreSQL internal
date datatype to the HL7
ts . |
5.3.10.3. Flavors of ‘ts’¶
The ts_date
flavor constrains ts
with a maximum precision of a date, and no timezone:
SELECT '200812'::ts_date;
SELECT '2008120112'::ts_date;
ts_date
----------
200812
(1 row)
ERROR: value for domain ts_date violates check constraint "ts_date_prcs_le_8_no_tz"
The ts_date_full
flavor constrains ts
so that it contains a reference to
a particular day, having exactly the precision of a date, and no timezone:
SELECT '20081217'::ts_date;
ts_date
----------
20081217
(1 row)
The ts_datetime
flavor constrains ts
so that it cannot contain a
fractional part. This limits the maximum precision to seconds:
SELECT '20081217173859'::ts_datetime;
ts_datetime
----------------
20081217183859
(1 row)
The ts_datetime_full
flavor constrains ts
so that it
contains a reference to a particular second with a timezone, having exactly the precision of seconds:
SELECT '20081217173759+0100'::ts_datetime_full;
ts_datetime_full
---------------------
20081217173759+0100
(1 row)
The ts_birth
flavor constrains ts
so it can only
contain a birth date. This means it can contain either a year, a particular day
or a fully specified date:
SELECT '2008'::ts_birth as birthyear, '20081217'::ts_birth as birthday, '20081217131241'::ts_birth as birth_full;
birthyear | birthday | birth_full
-----------+----------+----------------
2008 | 20081217 | 20081217141241
(1 row)
5.3.11. Interval of Point in Time (ivl_ts)¶
An interval of time is a set of consecutive values of timestamps, and can
be used to define periods of time. Together with the ts
and
qset_ts
Healthcare Datatypes, ivl_ts
enables a
wealth of operations that allow powerfull temporal expressions. These
expressions can be used to query data, and also be stored in table columns.
5.3.11.1. ‘ivl_ts’ literal Form¶
The ivl_ts
has seven different literal forms for input. When
operations are performed on intervals, the result will is in interval form.
Name | Example | Description |
---|---|---|
Interval form | [20000816102108;20010616192108] | Interval |
Comparator form | <20080101 | Comparator form |
Centerwidth form | 20010704145108 [300s] | Centerwidth form |
Width form | [10d] | Width form |
Center form | 20110704 | Center form |
Any form | ?200101? | Any form |
Hull form | 20010101..20101231 | Hull form |
5.3.11.1.1. Interval¶
The interval form of ivl_ts
uses brackets to denote the boundaries
of the interval and a ;
as separator. An example of this
form denoting the interval between 2008, the first of january, 13:12:51 and the
31st of januari, time 15:56:29:
SELECT '[20080101131251;20080131155629]'::ivl_ts;
ivl_ts
---------------------------------
[20080101131251;20080131155629]
(1 row)
The precision of the interval boundary is irrelevant for the interval. This means that the interval [20010101;20010131] is not the whole month of january in 2001. The proper way of denoting an entire calendar month is to use an open high boundary, e.g.: [20010101;20010201[
5.3.11.1.2. Comparator form¶
The comparator form of ivl_ts
uses <, <=, > or >= to indicate one
of the two boundaries of the interval. The other boundary is always positive or
negative infinity. Examples:
SELECT '<20080101'::ivl_ts;
SELECT '<=20080101'::ivl_ts;
ivl_ts
----------------------------
]NullFlavor.NINF;20080101[
(1 row)
ivl_ts
----------------------------
]NullFlavor.NINF;20080101]
(1 row)
5.3.11.1.3. Centerwidth form¶
The centerwidth form of ivl_ts
consists of two parts. The first
part indicates the center of the interval. The second part indicates the width
of the interval using a pq_time
, that is described in Flavors of ‘pq’. The second part uses brackets for its boundaries, and is always
output in number of seconds.
Examples:
SELECT '20010115135108 [10s]'::ivl_ts;
ivl_ts
---------------------
20010115135108[10s]
(1 row)
5.3.11.1.4. Width form¶
The width form of ivl_ts
is used to denote an interval with a
known width, but with unknown boundaries. This form consists of the width (a``pq_time``, Flavors of ‘pq’) in brackets, and is always
output as number of seconds:
SELECT '[10d]'::ivl_ts;
ivl_ts
-----------
[864000s]
(1 row)
5.3.11.1.5. Center form¶
The center form of ivl_ts
is used to denote an interval of which
only the center position is known:
SELECT '20010101'::ivl_ts;
ivl_ts
----------
20010101
(1 row)
Note that operators that require widths to function will return NullFlavor.UNK
when presented with a center form ivl_ts
.
5.3.11.1.6. Any form¶
ivl_ts
has a special literal form for the HL7 concept of
“contained value” (any). This means that only part of the interval is known,
e.g. to indicate that something has happened some time in january 2001 could be
written as:
SELECT '?200101?'::ivl_ts;
ivl_ts
----------
?200101?
(1 row)
Note that operators that require widths to function will return NullFlavor.UNK
when presented with a any form ivl_ts
.
5.3.11.1.7. Hull form¶
The hull format provides for a simpler way to denote intervals compared to the
interval form. The hull form is bracketless and uses ..
as a
separator. The entire first month of 2001 can be denoted as
“20010101..20010131”, which includes the whole last day of january. Thus the
(hull form) interval “20010101..20010131” equals the (interval form) interval
[20010101;20010201[.
The hull-form of ivl_ts
offers an abbreviation where it is not
necessary for the higher timestamp literal to repeat the digits on the left
that are the same of the lower timestamp literal. Some examples follow.
The first month of 2001:
SELECT '20010101..20010131'::ivl_ts;
ivl_ts
---------------------
[20010101;20010201[
(1 row)
The first two months of 2001:
SELECT '[20010101;2001010301['::ivl_ts as "interval", '20010101..20010228'::ivl_ts as "hull", '20010101..0228'::ivl_ts as "hull abbrev";
interval | hull | hull abbrev
-------------------------+---------------------+---------------------
[2001010100;2001010301[ | [20010101;20010301[ | [20010101;20010301[
(1 row)
5.3.11.2. Functions on ‘ivl_ts’¶
The functions on ivl_ts
, listed in Functions on ‘ivl_ts’, can be used to create new temporal datavalues,
and also to compare values.
Operat or | Function | Argume nts | Return s | Description |
---|---|---|---|---|
promotion | ts | ivl_t s | Promotes a ts to an interval. This
function is used when a timestamp is
casted to an interval. |
|
demotion | ivl_t s | ts | Demotes an interval to a ts . When both
boundaries are finite, the result is the
timestamp at the center of the interval.
Demotion of a interval with an infinite
boundary, the result is the other
boundary. Demotion of
[NullFlavor.NINF;NullFlavor.PINF]
results in a timestamp with
NullFlavor.NA . |
|
= | equal | ivl_t s, ivl_t s | bl | Two ivl_ts are equals if both their
values and the integer part of their
precision match |
<> | notequal | ivl_t s, ivl_t s | bl | |
~ | contains | ivl_t s, ts | bl | Returns true if the first operand contains the second operand. |
~ | contains | ivl_t s, ivl _ts | bl | Returns true if the first operand contains the second operand. |
@ | contained | ts, ivl_t s | bl | Return true if the first operand is contained in the second operand |
@ | contained | ivl_t s, ivl_t s | bl | Return true if the first operand is contained in the second operand |
&& | overlaps | ivl_t s, ivl_t s | bl | Return true if the first operand overlaps with the second operand |
intervalafte r | ivl_t s, ts | ivl_t s | Returns the part of the first operand that
starts after the ts in the second
operand. If there is no overlap,
NullFlavor.NA is returned to indicate
there is no interval result. |
|
intervalbefo re | ivl_t s, ts | ivl_t s | Returns the part of the first operand that
end before the ts in the second
operand. If there is no overlap,
NullFlavor.NA is returned to indicate
there is no interval result. |
|
convexhull | ivl_t s, ivl_t s | ivl_t s | Returns the least interval that is a superset of both operands | |
lowvalue | ivl_t s | ts | Returns the lower limit of the interval | |
highvalue | ivl_t s | ts | Returns the upper limit of the interval | |
anyvalue | ivl_t s | ts | Returns the any value of the input
interval in ts format. |
|
width | ivl_t s | pq | Returns the difference between the high
and low boundary. The result is a PQ |
|
centervalue | ivl_t s | ts | Returns the ts at the center of the
interval |
|
lowclosed | ivl_t s | boolea n | Returns true if the low boundary is closed | |
highclosed | ivl_t s | boolea n | Returns true if the high boundary is closed |
Now follow examples for each of the functions defined above. Some of the examples use this example table:
CREATE TABLE intervals (a ivl_ts);
INSERT INTO intervals VALUES
('[20010101;20010301['),
('200101..02'),
('[NullFlavor.NINF;20010430]');
SELECT * FROM intervals;
CREATE TABLE
INSERT 0 3
a
----------------------------
[20010101;20010301[
[200101;200103[
[NullFlavor.NINF;20010430]
(3 rows)
The comparator literal form (Comparator form) can be used together with the
&&
overlaps operator to query the database for
intervals that are before, or overlap with a certain timestamp or interval:
SELECT * FROM intervals WHERE a && '<=19981010';
a
----------------------------
[NullFlavor.NINF;20010430]
(1 row)
The promotion function is used when a timestamp is cast to an interval of time:
SELECT '20010131'::ts::ivl_ts AS "ivl";
ivl
---------------------
[20010131;20010201[
(1 row)
Demotion of intervals to timestamps with a certain precision. Note that the demotion of an interval with an infinite boundary is not the timestamp at the center. See table Functions on ‘ivl_ts’ for a discussion.
SELECT a, demotion(a) FROM intervals;
a | demotion
----------------------------+----------
[20010101;20010301[ | 20010130
[200101;200103[ | 200101
[NullFlavor.NINF;20010430] | 20010430
(3 rows)
Equal and notequal:
SELECT a, equal(a,'[20010101;20010301['), notequal(a,'[20010101;20010301[') FROM intervals;
a | equal | notequal
----------------------------+-------+----------
[20010101;20010301[ | true | false
[200101;200103[ | true | false
[NullFlavor.NINF;20010430] | false | true
(3 rows)
Contains and contained:
SELECT a, contains(a,'[20010118;20010131]'::ivl_ts), contained(a,'[20000101;20010501]'::ivl_ts) FROM intervals;
a | contains | contained
----------------------------+----------+-----------
[20010101;20010301[ | true | true
[200101;200103[ | true | true
[NullFlavor.NINF;20010430] | true | false
(3 rows)
Intervalbefore and intervalafter:
SELECT a, intervalafter(a,'20010203'), intervalbefore(a,'20010203') FROM intervals;
a | intervalafter | intervalbefore
----------------------------+---------------------+----------------------------
[20010101;20010301[ | ]20010203;20010301[ | [20010101;20010203[
[200101;200103[ | ]20010203;200103[ | [200101;20010203[
[NullFlavor.NINF;20010430] | ]20010203;20010430] | [NullFlavor.NINF;20010203[
(3 rows)
Convex hull, lowvalue and highvalue:
SELECT a, convexhull(a, '20010805..1231'), lowvalue(a), highvalue(a) FROM intervals;
a | convexhull | lowvalue | highvalue
----------------------------+----------------------------+-----------------+-----------
[20010101;20010301[ | [20010101;20020101[ | 20010101 | 20010301
[200101;200103[ | [20010101;20020101[ | 200101 | 200103
[NullFlavor.NINF;20010430] | [NullFlavor.NINF;20020101[ | NullFlavor.NINF | 20010430
(3 rows)
Anyvalue:
SELECT anyvalue('?2002?'::ivl_ts);
anyvalue
----------
2002
(1 row)
Width, centervalue, lowclosed and highclosed:
SELECT a, width(a), canonical(width(a)/'86400 s') AS width_days, centervalue(a),lowclosed(a),highclosed(a) FROM intervals;
a | width | width_days | centervalue | lowclosed | highclosed
----------------------------+-----------+------------+-----------------+-----------+------------
[20010101;20010301[ | 5097600 s | 59 | 20010130 | t | f
[200101;200103[ | 5097600 s | 59 | 200101 | t | f
[NullFlavor.NINF;20010430] | | | NullFlavor.NINF | t | t
(3 rows)
An extra example using the highvalue and peforms further calculation with the
resulting ts
, which is automatically cast to Timestamp with
timezone
:
SELECT highvalue('2002..2003'::ivl_ts) + interval '2 days';
?column?
------------------------
2004-01-03 00:00:00+01
(1 row)
5.3.12. Interval of Physical Quantity (ivl_pq)¶
A set of consecutive values of physical quantities.
5.3.12.1. ivl_pq Literal Form¶
The ivl_pq
literal has a number of different formats which are described below.
Note
On output of an interval, the physical quantity values and units will be
displayed in their canonical form. For instance, this means that on
output, prefixes such as m
for milli are not used, but
the value shown is divided by 1000. Also, an interval with the unit
Hz
will be displayed using
s-1
. Also annotations like {bodyweight} are not shown
on output. This behaviour is likely to change in a future version.
5.3.12.1.1. Interval form¶
The interval form of ivl_pq
uses brackets to denote the boundaries
of the interval and a ;
as separator. An example of this
form denoting the interval between 2 mm and 5 mm:
SELECT '[2mm;5mm]'::ivl_pq;
SELECT '[10 [gal_us];10 [gal_br]]'::ivl_pq;
ivl_pq
-------------------
[0.002 m;0.005 m]
(1 row)
An interval containing the difference between the British Imperial gallon and the U.S. gallon:
ivl_pq
---------------------------------
[0.03785411784 m3;0.0454609 m3]
(1 row)
5.3.12.1.2. Dash form¶
The dash form of ivl_pq
does not use brackets, only a``-`` as separator. An example of the dash form:
select '2mm-5mm'::ivl_pq;
ivl_pq
-------------------
[0.002 m;0.005 m]
(1 row)
5.3.12.1.3. Simple form¶
Because both boundaries of a ivl_pq
have the same unit, the unit
can be factored out of the brackets. This leads to the simple form of``ivl_pq``. Examples of the simple form:
SELECT '[20;20000] Hz'::ivl_pq;
SELECT '[1500;2000] [nmi_i]'::ivl_pq as nautical_miles;
ivl_pq
--------------------
[20 s-1;20000 s-1]
(1 row)
nautical_miles
-----------------------
[2778000 m;3704000 m]
(1 row)
5.3.12.1.4. Comparator form¶
The comparator form of ivl_pq
uses <, <=, > or >= to indicate one
of the two boundaries of the interval. The other boundary is always positive or
negative infinity. Examples:
select '<3hPa'::ivl_pq;
SELECT '>= 100mm[Hg]'::ivl_pq;
ivl_pq
-----------------------------------
]-inf m-1.g.s-2;300000 m-1.g.s-2[
(1 row)
ivl_pq
------------------------------------
[13332200 m-1.g.s-2;inf m-1.g.s-2[
(1 row)
5.3.12.1.5. Centerwidth form¶
The centerwidth form of ivl_pq
consists of two parts. The first
part is a pq
that indicates the center of the interval. The second
part is a pq
that indicates the width of the interval. The second
part uses brackets for its boundaries and should use the same (canonical) unit
as the first. Examples:
SELECT '2mm [3m]'::ivl_pq;
SELECT '2mm [3l]'::ivl_pq;
ivl_pq
--------------------
[-1.498 m;1.502 m]
(1 row)
ERROR: interval unit mismatch: ivl_pq is of type 'm3', not of type 'm'
LINE 1: select '2mm [3l]'::ivl_pq;
5.3.12.1.6. Width form¶
The width form of ivl_pq
is used to denote an interval with a
known width, but with unknown boundaries. This form consists of the width (a``pq``) in brackets:
SELECT '[500mbar]'::ivl_pq;
ivl_pq
----------------------
[50000000 m-1.g.s-2]
(1 row)
5.3.12.1.7. Center form¶
The center form of ivl_pq
is used to denote an interval of which
only the center position is known:
SELECT '100kg'::ivl_pq;
ivl_pq
----------
100000 g
(1 row)
5.3.12.1.8. Any form¶
ivl_pq has a special literal form for the concept of “contained value” (any). This means that only part of the interval is known, e.g. to indicate that the only thing known about a interval is that it contains the value``50ml`` the any form can be used:
SELECT '?50ml?'::ivl_pq;
ivl_pq
------------
?5e-05 m3?
(1 row)
5.3.12.2. Functions on ‘ivl_pq’¶
The functions on ivl_pq
can be found in
Functions on ‘ivl_pq’. All of these functions are derived from
the general ivl
type (Interval specializes QSET).
Operat or | Function | Argume nts | Return s | Description |
---|---|---|---|---|
promotion | pq | ivl_p q | Promotion is not supported. To promote a
physical quantity to an interval, pq would
need to explicitly store the precision, so
it can differentiate between e.g.
1.00 m and 1 m .
Since that is not specified
by the Healthcare Datatype standard for
physical quantity, promotion to ivl_pq
is not possible. |
|
demotion | ivl_p q | pq | Demotes a ivl_pq to a pq . Demotion
of a finite interval results in a pq
that denotes the center of the interval.
Demotion of a interval with one non-finite
end results in a pq that denotes the
finite end of the interval. Demotion of
[NullFlavor.NINF;NullFlavor.PINF] will
result in an error. |
|
= | equal | ivl_p q, ivl_p q | bl | Two ivl_pq are equals if both their
values and the integer part of their
precision match |
<> | notequal | ivl_p q, ivl_p q | bl | Two ivl_pq are equals if both their
values and the integer part of their
precision match |
~ | contains | ivl_p q, pq | bl | Returns true if the pq value of the
second operand falls within the interval
in the first operand. |
~ | contains | ivl_p q, ivl_p q | bl | Returns true if the second operand falls within the first operand. |
@ | contained | ivl_p q, ivl_p q | bl | Return true if the first operand starts and stops in the interval of the second operand. |
lowclosed | ivl_p q | boolea n | Returns true if the low boundary is closed. | |
highclosed | ivl_p q | boolea n | Returns true if the high boundary is closed. | |
&& | overlapsbool | ivl_p q, ivl_p q | boolea n | Returns true if there is an overlap between the intervals of the two operands. |
lowclosed | ivl_p q | boolea n | Returns true if the low boundary is closed. | |
highclosed | ivl_p q | boolea n | Returns true if the high boundary is closed. | |
intervalafte r | ivl_p q, pq | ivl_p q | Returns the part of the first operand that
starts after the pq in the second
operand. |
|
intervalbefo re | ivl_p q, pq | ivl_p q | Returns the part of the first operand that
end before the pq in the second
operand. |
|
convex hull | ivl_p q, ivl_p q | ivl_p q | Returns the least interval that is a superset of both operands | |
lowvalue | ivl_p q | pq | Returns the lower limit of the interval | |
highvalue | ivl_p q | pq | Returns the upper limit of the interval | |
anyvalue | ivl_p q | pq | Returns the any value of the input
interval in pq format. |
|
width | ivl_p q | pq | Returns the difference between the high
and low boundary. The result is a PQ |
|
centervalue | ivl_p q | pq | Returns the pq at the center of the
interval |
Now follow examples for each of the functions defined above. Some of the examples use an following example table:
CREATE TABLE intervals_pq (a ivl_pq);
INSERT INTO intervals_pq VALUES ('[3mm;5mm['),('100mm[Hg]-120mm[Hg]'),('[50;80] kg{bodyweight}'), ('[105;150] mm[Hg]');
SELECT * FROM intervals_pq;
CREATE TABLE
INSERT 0 4
a
-----------------------------------------
[0.003 m;0.005 m[
[13332200 m-1.g.s-2;15998640 m-1.g.s-2]
[50000 g;80000 g]
[13998810 m-1.g.s-2;19998300 m-1.g.s-2]
(4 rows)
Demotion:
SELECT a, demotion(a) FROM intervals_pq;
a | demotion
-----------------------------------------+--------------------
[0.003 m;0.005 m[ | 0.004 m
[13332200 m-1.g.s-2;15998640 m-1.g.s-2] | 14665420 m-1.g.s-2
[50000 g;80000 g] | 65000 g
[13998810 m-1.g.s-2;19998300 m-1.g.s-2] | 16998555 m-1.g.s-2
(4 rows)
Equal and notequal. If units do not compare, the NA nullflavor is returned.
SELECT a, equal(a, '50kg-80kg'), notequal(a, '50kg-80kg') FROM intervals_pq;
a | equal | notequal
-----------------------------------------+---------------+---------------
[0.003 m;0.005 m[ | NullFlavor.NA | NullFlavor.NA
[13332200 m-1.g.s-2;15998640 m-1.g.s-2] | NullFlavor.NA | NullFlavor.NA
[50000 g;80000 g] | true | false
[13998810 m-1.g.s-2;19998300 m-1.g.s-2] | NullFlavor.NA | NullFlavor.NA
(4 rows)
Contains and contained:
SELECT a, contains(a,'[110mm[Hg];115mm[Hg]]'::ivl_pq), contained(a,'[102mm[Hg];160mm[Hg]]'::ivl_pq) FROM intervals_pq;
a | contains | contained
-----------------------------------------+---------------+---------------
[0.003 m;0.005 m[ | NullFlavor.NA | NullFlavor.NA
[13332200 m-1.g.s-2;15998640 m-1.g.s-2] | true | false
[50000 g;80000 g] | NullFlavor.NA | NullFlavor.NA
[13998810 m-1.g.s-2;19998300 m-1.g.s-2] | true | true
(4 rows)
Intervalbefore and intervalafter:
SELECT a, intervalafter(a, '4 mm'::pq), intervalbefore(a, '4 mm'::pq) FROM intervals_pq;
a | intervalafter | intervalbefore
-----------------------------------------+-------------------+-------------------
[0.003 m;0.005 m[ | ]0.004 m;0.005 m[ | [0.003 m;0.004 m[
[13332200 m-1.g.s-2;15998640 m-1.g.s-2] | NullFlavor.NA | NullFlavor.NA
[50000 g;80000 g] | NullFlavor.NA | NullFlavor.NA
[13998810 m-1.g.s-2;19998300 m-1.g.s-2] | NullFlavor.NA | NullFlavor.NA
Convex hull
SELECT a, convexhull(a , '[4;10] mm'::ivl_pq) FROM intervals_pq;
a | convexhull
-----------------------------------------+------------------
[0.003 m;0.005 m[ | [0.003 m;0.01 m]
[13332200 m-1.g.s-2;15998640 m-1.g.s-2] | NullFlavor.NA
[50000 g;80000 g] | NullFlavor.NA
[13998810 m-1.g.s-2;19998300 m-1.g.s-2] | NullFlavor.NA
(4 rows)
Lowvalue and highvalue:
SELECT a, lowvalue(a), highvalue(a) FROM intervals_pq;
a | lowvalue | highvalue
-----------------------------------------+--------------------+--------------------
[0.003 m;0.005 m[ | 0.003 m | 0.005 m
[13332200 m-1.g.s-2;15998640 m-1.g.s-2] | 13332200 m-1.g.s-2 | 15998640 m-1.g.s-2
[50000 g;80000 g] | 50000 g | 80000 g
[13998810 m-1.g.s-2;19998300 m-1.g.s-2] | 13998810 m-1.g.s-2 | 19998300 m-1.g.s-2
(4 rows)
Anyvalue:
SELECT anyvalue('?20 mm[Hg]?'::ivl_pq);
anyvalue
-------------------
2666440 m-1.g.s-2
(1 row)
Width, centervalue:
SELECT a, width(a), centervalue(a) FROM intervals_pq;
a | width | centervalue
-----------------------------------------+-------------------+--------------------
[0.003 m;0.005 m[ | 0.002 m | 0.004 m
[13332200 m-1.g.s-2;15998640 m-1.g.s-2] | 2666440 m-1.g.s-2 | 14665420 m-1.g.s-2
[50000 g;80000 g] | 30000 g | 65000 g
[13998810 m-1.g.s-2;19998300 m-1.g.s-2] | 5999490 m-1.g.s-2 | 16998555 m-1.g.s-2
(4 rows)
5.3.13. Continuous set of Point in Time (qset_ts)¶
qset_ts
is a set of time intervals, can be expressed as unions
and exceptions of ivl_ts
.
5.3.13.1. ‘qset_ts’ Literal Form¶
A qset_ts
can be constructed using an expression of unions
and exclusions that corresponding to the qset_ts
grammar definition, where each element is a ivl_ts
supplied
in interval form (Interval). Unions
and exclusions are specified using the infix binary operators
;
and \
respectively. Intersections
are specified using a single whitespace between operands. Intersection
takes precendence over union, and union takes precedence over
exclusion. Parentheses can be used to force precedence. Note that the
hull is not supported at this point. For GTS support
with PIVL_TS
, see Complex type array instantiating of a GTS value.
Examples: the years 2000 to 2001, and 2003 to 2004.
SELECT '[2000;2001];[2003;2004]'::qset_ts;
qset_ts
-------------------------
[2000;2001];[2003;2004]
(1 row)
the years 2000 to 2010 except 2003
SELECT '2000 .. 2010'::ivl_ts::qset_ts - '2003'::ivl_ts AS exceptexample;
exceptexample
--------------------
([2000;2011[\2003)
(1 row)
qset_ts
has a literal form that is generated during
the parse phase. The original literal form is lost when
the qset_ts
is used as an operand in the functions
described below. Then the literal form is set to the canonical form
consisting of unions of disjunct ivl_ts
.
5.3.13.2. ‘qset_ts’ Functions¶
Op | Function | Arguments | Retu rns | Description |
---|---|---|---|---|
= | equal | qset_ts, qset_ts | bool | Two qset_ts are equal if
they specify the same canonical
time intervals. Note that
different literal forms may lead
to the same canonical
qset_ts . |
<> | notequal | qset_ts, qset_ts | bool | Two qset_ts are not equal if
they specify different canonical
time intervals. |
qset_tsunion | qset_ts, qset_ts | qset _ts | Returns the union of the two operands. | |
qset_tsunionivl_ts | qset_ts, ivl_ts | qset _ts | Returns the union of the two operands. | |
sum( ) | qset_tsunionivl_ts | qset_ts, ivl_ts | qset _ts | Aggregate that returns the union of the operands. |
qset_tsexcept | qset_ts, qset_ts | qset _ts | Returns the exclusion of the second operand from the first. | |
& | qset_tsintersect | qset_ts, qset_ts | qset _ts | Returns the intersection of the operands. |
ivl_ts2qset_ts | ivl_ts | qset _ts | Cast an ivl_ts into a qset_ts. | |
qset_ts2_ivl_ts | qset_ts | ivl_ts[ ] | Cast a qset_ts into an array of ivl_ts. | |
ts2qset_ts | ts | qset _ts | Promote a ts and return the result as a qset_ts. | |
canonical | qset_ts | qset _ts | Returns the canonical value of the operand as qset_ts. |
5.3.13.3. ‘qset_ts’ Examples¶
The first example shows the sum()
aggregate with
query results in the canonical form, i.e. a qset_ts
literal
that is the union of disjunct ivl_ts
. The second
example shows an explicit cast to an array of ivl_ts
. The
third example shows how two different qset_ts
literals can
specify the same canonical interval.
CREATE TABLE medication (name text, effectivetime ivl_ts);
INSERT INTO medication VALUES ('Scott', '[20100316;20100514]');
INSERT INTO medication VALUES ('Scott', '[20100420;20100701]');
INSERT INTO medication VALUES ('Scott', '[20101220;20110119]');
INSERT INTO medication VALUES ('Julia', '[20100516;20100614]');
INSERT INTO medication VALUES ('Julia', '[20100620;20100801]');
INSERT INTO medication VALUES ('Julia', '[20101220;20110119]');
SELECT name, '2010' - sum(effectivetime) AS nomeds
FROM medication
GROUP BY name;
SELECT '[2000;2001];[2001;2002]\<2001'::qset_ts::ivl_ts[];
select '2010..1'::qset_ts = '2010;2011' as equal;
name | nomeds
--------+------------------------------------------------------------
Scott | [20100101;20100316[;]20100701;20101220[
Julia | [20100101;20100516[;]20100614;20100620[;]20100801;20101220[
(2 rows)
ivl_ts
---------------
{]2001;2002]}
(1 row)
equal
-------
t
(1 row)
5.4. Context conduction¶
There are two kinds of context conduction defined on the RIM. The former conduction-indicator-based (C) is deprecated since RIM 2.30 and replaced by the new vocabulary-based (V) context conduction.
5.4.1. Conduction Indicator Based¶
MGRID HDM and XFM provide two implementations for conduction indicator based context conduction.
- The extension
hl7v3_c_contextconduction_edition2011
implements a trigger based implementation, where the additional context records are added immediately after insertion or update of records. The drawback of the trigger based approach is that this method is not suitable for large datasets. - The XFM module uploads data to the lake in microbatches. Before
upload, the pre-processing SQL
script
./pond/preprocess/010_ccontextconduction.sql
adds context records for all the documents in the microbatch to upload. This method is faster than the trigger based implementation.
5.4.2. Vocabulary Based¶
MGRID HDM and XFM do not provide implementations for vocabulary based context conduction, required for Normative Editions 2012 and later.
6. Index support¶
The following query shows all the operator classes available for the Healthcare Datatypes. See section Using indexes for examples how to to create and use indexes.
SELECT opc.opcintype::regtype::text COLLATE "C" AS indexed_type,
am.amname AS index_method,
opc.opcname AS opclass_name,
opc.opcdefault AS is_default,
(SELECT string_agg(x,' ') FROM (
SELECT substring(amop.amopopr::regoperator::text from '^.*(?=\()') AS x
FROM pg_amop amop
WHERE amop.amopfamily = opc.opcfamily
AND amop.amopmethod = opc.opcmethod
AND substring(amop.amopopr::regoperator::text from '^.*(?=\()') NOT IN ('#<#', '#<=#', '#>=#', '#>#', '#=#')
)AS operators) AS operators
FROM pg_am am, pg_opclass opc
WHERE opc.opcmethod = am.oid
AND 'hl7' = (
SELECT nspname
FROM pg_namespace nsp
JOIN pg_type typ
ON typnamespace = nsp.oid
AND typ.oid = opc.opcintype)
ORDER BY indexed_type, index_method, opclass_name;
indexed_type | index_method | opclass_name | is_default | operators
--------------+--------------+---------------------+------------+-------------
"ANY" | btree | btree_ANY_ops | t | #<# #<=# =
"ANY" | gin | gin_ANY_hash_ops | f | @>
"ANY" | gin | gin_ANY_ops | t | @>
"ANY" | gist | gist_ANY_ops | t | @>
"ANY" | gist | gist_SET_II_ops | f | &&
"ANY" | hash | hash_ANY_ops | t | =
bl | btree | bl_ops | t | < <= = >= >
bn | btree | bn_ops | t | < <= = >= >
bn | hash | hash_bn_ops | t | =
cv | btree | cv_ops | t | #<#
cv | gist | gist_cv_df | f | = >> <<
cv | gist | gist_cv_ops | t | = >> <<
cv | hash | hash_cv_ops | t | =
hl7."any" | btree | any_ops | t | #<#
ii | btree | ii_ops | t | #<#
ii | gist | gist_ii_ops | t | =
ii | hash | hash_ii_ops | t | =
ii[] | gin | gin__ii_ops | t | && <@ @> =
ivl_pq | btree | ivl_pq_ops | t | #<#
ivl_pq | gist | gist_ivl_pqops | t | && = ~ @
ivl_ts | btree | ivl_ts_ops | t | #<#
ivl_ts | gist | gist_ivl_tsops | t | && = ~ @
ivl_ts | gist | gist_ivl_tsunionops | f | && = ~ @
ivl_ts | hash | hash_ivl_ts_ops | t | =
pq | btree | pq_ops_equal | t | #<#
pq | btree | pq_ops_identical | f | ==
pq | gist | gist_pq_ops | t | < <= = >= >
pq | hash | hash_pq_ops | t | =
pq[] | gin | gin__pq_ops | t | && <@ @> =
qset_ts | btree | qset_ts_ops | t | #<#
rto | btree | rto_ops_equal | t | #<#
rto[] | gin | gin__rto_ops | t | && <@ @> =
ts | btree | ts_ops_equal | t | #<#
ts | btree | ts_ops_identical | f | ==
ts | gist | gist_ts_ops | t | < <= = >= >
ts | hash | hash_ts_ops | t | =
(36 rows)
[1] | The return type is always true or false, hence bn , though the
standard specifies bl as return type. |
[2] | HL7v3 codesystems have OIDs with the prefix 2.16.840.1.113883.5 |