T1.2: Designs Advanced Schemas

%Date Data Type

The %Date data type stores date-only values with a logical representation as an integer representing the number of days since December 31, 1840 (day 0).

• Storage format: Compact integer representation
• Date range: Supports dates from 0001-01-01 to 9999-12-31
• Pre-1840 dates: The epoch (day 0) is 1840-12-31, but the integer is signed — dates before 1840-12-31 are stored as negative day counts. For example, 0001-01-01 is roughly day -671,861. This is why the supported range extends backwards well before 1840.
• Display format: Locale-specific formats or ODBC format (YYYY-MM-DD)
• SqlCategory: DATE

%Time Data Type

The %Time data type represents time-of-day values, storing the logical value as the number of seconds past midnight.

• Value range: 0 to 86399 seconds
• Display format: HH:MM:SS
• SqlCategory: TIME
• Use case: Efficient time arithmetic and comparisons

%TimeStamp Data Type

The %TimeStamp data type combines both date and time components into a single value.

• Format: YYYY-MM-DD HH:MM:SS.nnnnnnnnn
• Precision: Up to nine digits of fractional seconds
• Conversion: Logical value obtained from $HOROLOG format using $ZDATETIME(h,3)
• SqlCategory: TIMESTAMP
• Use case: Recording exact moments in time (standard ODBC timestamp format)

%PosixTime Data Type

The %PosixTime data type offers an alternative timestamp representation using Unix epoch encoding.

• Storage: Encoded 64-bit signed integer representing elapsed seconds with microsecond precision
• Epoch: 1970-01-01 00:00:00 (Unix epoch, NOT the 1840 %Date epoch). The encoded form at the epoch is 1152921504606846976; the encoding packs the sign, seconds, and microseconds into 64 bits.
• Date range: 0001-01-01 to 9999-12-31 (same span as other temporal types, achieved via the 64-bit signed integer range)
• Precision: Exactly 6 digits of fractional seconds (microseconds)
• SqlCategory: POSIXTS
• Note: Dates before 1970 map to negative signed values in the encoding. %PosixTime and %Date use different epochs, so do not convert between them by simple subtraction — use TO_POSIXTIME() / TO_DATE() for correctness.

SqlCategory and Type Conversion

Each data type has a distinct SqlCategory classification that controls comparison operations.

• Conversion functions: Use DATE(), TO_TIMESTAMP(), or TO_POSIXTIME() when mixing temporal types in comparisons or indexes
• Schema design impact: Choice of temporal data type affects storage efficiency, query performance, and interoperability with external systems
• Recommendations:
• %Date and %Time: Most compact storage for date-only or time-only values
• %TimeStamp: Human-readable precision timestamps
• %PosixTime: Unix timestamp compatibility and high-precision temporal calculations

list Of %String — inline storage

Class App.Patient Extends %Persistent {
  Property Name As %String;
  Property Allergies As list Of %String;
}

SQL view of the table:

ID	Name	Allergies
1	Smith	$LB("Penicillin","Peanut")

The Allergies column holds the $LISTBUILD-encoded blob directly; there is no child table. To search:

SELECT ID, Name FROM App.Patient
 WHERE FOR SOME %ELEMENT(Allergies) (%Value = 'Penicillin')

array Of %String — child table storage

Class App.Patient Extends %Persistent {
  Property Name As %String;
  Property Phones As array Of %String;
}

IRIS creates a child table App.Patient_Phones with columns (Patient, element_key, Phones). Each phone is one row. Standard SQL JOIN/WHERE works against the child table.

Comparison

Aspect	list Of	array Of
SQL storage	inline column, $LISTBUILD-encoded	separate child table
Keyed?	positional (ordinal)	keyed (arbitrary key per element)
Typical use	small, fixed-form multi-value (allergies, tags)	keyed attributes (phone numbers by type)
Searching	FOR SOME %ELEMENT	JOIN on child table
Indexing	collection-element index on parent	index on child table

Class App.Address Extends %SerialObject {
  Property Street As %String;
  Property City As %String;
}

Class App.Person Extends %Persistent {
  Property Name As %String;
  Property HomeAddress As App.Address;
}

SQL projection of App.Person:

ID	Name	HomeAddress_Street	HomeAddress_City
1	Smith	42 Main St	Boston

To query a field inside the serial: SELECT Name, HomeAddress_City FROM App.Person. The underscore-flattened columns are SQL-addressable; the App.Address class itself is not a table.

Class App.Document Extends %Persistent {
  Property Title As %String;
  Property Content As %Stream.GlobalCharacter;
  Index ci On (Content) As %iFind.Index.Basic;
}

Typical operations:

-- OK: retrieve stream (client assembles the stream from the cursor)
SELECT Title, Content FROM App.Document WHERE ID = 1

-- NOT supported: WHERE on a stream column directly
SELECT * FROM App.Document WHERE Content LIKE '%confidential%'   -- will error or return wrong results

-- Correct way: %CONTAINS with iFind index
SELECT ID, Title FROM App.Document WHERE %ID %FIND search_index(ci, 'confidential')

// FullName — always fresh, recomputed on read, not indexable
Property FullName As %String [ Calculated,
  SqlComputeCode = {Set {*} = {FirstName}_" "_{LastName}},
  SqlComputed ];

// Age — stored, auto-updated when DateOfBirth changes, indexable
Property Age As %Integer [
  SqlComputed,
  SqlComputeCode = {Set {*} = $SYSTEM.SQL.Functions.DATEDIFF("yy",{DateOfBirth},$HOROLOG)},
  SqlComputeOnChange = DateOfBirth ];
Index AgeIdx On Age;

For a 10M-row table queried frequently on WHERE Age = 30, the second form is correct — the computed value is materialised into the row, and an index on Age makes the lookup O(log n) instead of full table scan.

Columnar storage is configurable directly in DDL — no class definition required.

-- Mixed: row-storage table with columnar storage on the columns that drive aggregation
CREATE TABLE App.Sale (
  OrderId  INTEGER PRIMARY KEY,
  RegionId INTEGER,
  Amount   NUMERIC(18,2) WITH STORAGETYPE = COLUMNAR,
  Quantity INTEGER       WITH STORAGETYPE = COLUMNAR
);

-- Full columnar table — every column columnar, best for analytic-only workloads
CREATE TABLE App.SalesFact (
  Id       BIGINT PRIMARY KEY,
  RegionId INTEGER,
  Amount   NUMERIC(18,2),
  Quantity INTEGER
) WITH STORAGETYPE = COLUMNAR;

-- Add a new columnar column to an existing table
ALTER TABLE App.Sale ADD COLUMN Discount NUMERIC(18,2) WITH STORAGETYPE = COLUMNAR;

The same effect can be expressed in an ObjectScript class definition (Property Amount As %Numeric(SCALE=2) [ Columnar ] for a column, or class-level [ StorageStrategy = Columnar ] for a full columnar table) — both routes produce the same physical layout.

Class App.Order Extends %Persistent [ VersionProperty = Version ] {
  Property Quantity As %Integer;
  Property Version As %Integer [ InitialExpression = 0 ];
}

Application pattern:

-- Read
SELECT Quantity, Version INTO :qty, :ver FROM App.Order WHERE ID = :id

-- ... user edits ...

-- Update, guarded by the version
UPDATE App.Order SET Quantity = :newQty WHERE ID = :id AND Version = :ver
IF %ROWCOUNT = 0 THEN
   -- lost-update: another session modified the row
END