IBM i: The SQL Way · #8

Convert an Internal IBM i System Timestamp to an SQL Timestamp

Use FROM_SYSTEM_TIMESTAMP to convert an eight-byte internal IBM i system timestamp into a readable SQL TIMESTAMP value.

Some IBM i interfaces return a timestamp as an eight-byte internal system value rather than an SQL TIMESTAMP. FROM_SYSTEM_TIMESTAMP converts that value into a readable timestamp that can be filtered, formatted, compared, and stored using SQL.

The function is:

FROM_SYSTEM_TIMESTAMP

It accepts an eight-byte internal system timestamp represented as:

CHAR(8)

and returns:

TIMESTAMP(6)

Basic example

VALUES FROM_SYSTEM_TIMESTAMP(
    X'AEAA174D8BDF4001'
);

Result:

2026-01-05 11:52:04.417012

The hexadecimal literal represents the underlying eight-byte system timestamp.

Show both the raw and converted values

WITH SOURCE_VALUE (SYSTEM_TIMESTAMP_VALUE) AS
(
    VALUES CAST(
        X'AEAA174D8BDF4001'
        AS CHAR(8) FOR BIT DATA
    )
)
SELECT
    HEX(SYSTEM_TIMESTAMP_VALUE)
        AS RAW_SYSTEM_TIMESTAMP,
    FROM_SYSTEM_TIMESTAMP(SYSTEM_TIMESTAMP_VALUE)
        AS SQL_TIMESTAMP
FROM SOURCE_VALUE;

This is useful while troubleshooting because it preserves the original value beside the converted result.

Convert a column in a table

Assume a table stores an internal system timestamp in a binary-compatible character column:

SELECT
    EVENT_ID,
    HEX(INTERNAL_EVENT_TIMESTAMP)
        AS INTERNAL_TIMESTAMP_HEX,
    FROM_SYSTEM_TIMESTAMP(
        INTERNAL_EVENT_TIMESTAMP
    ) AS EVENT_TIMESTAMP
FROM MYLIB.EVENT_LOG
ORDER BY EVENT_TIMESTAMP DESC;

The converted value can be used like any other SQL timestamp.

Filter by a date range

SELECT
    EVENT_ID,
    EVENT_TYPE,
    FROM_SYSTEM_TIMESTAMP(
        INTERNAL_EVENT_TIMESTAMP
    ) AS EVENT_TIMESTAMP
FROM MYLIB.EVENT_LOG
WHERE FROM_SYSTEM_TIMESTAMP(
          INTERNAL_EVENT_TIMESTAMP
      ) >= CURRENT TIMESTAMP - 24 HOURS
ORDER BY EVENT_TIMESTAMP DESC;

When the table is large and the expression is evaluated frequently, consider whether the converted value should be stored in a generated column, maintained in a separate SQL timestamp column, or calculated only for a narrowed set of rows.

Format the converted timestamp

VALUES VARCHAR_FORMAT(
    FROM_SYSTEM_TIMESTAMP(
        X'AEAA174D8BDF4001'
    ),
    'YYYY-MM-DD HH24:MI:SS.FF6'
);

To return only a date:

VALUES DATE(
    FROM_SYSTEM_TIMESTAMP(
        X'AEAA174D8BDF4001'
    )
);

To format it for display:

VALUES VARCHAR_FORMAT(
    FROM_SYSTEM_TIMESTAMP(
        X'AEAA174D8BDF4001'
    ),
    'MM/DD/YYYY HH24:MI:SS'
);

Keep the original timestamp data type for calculations and filtering. Format it as character data only at the presentation boundary.

Use it in a view

CREATE OR REPLACE VIEW MYLIB.EVENT_LOG_READABLE AS
SELECT
    EVENT_ID,
    EVENT_TYPE,
    INTERNAL_EVENT_TIMESTAMP,
    FROM_SYSTEM_TIMESTAMP(
        INTERNAL_EVENT_TIMESTAMP
    ) AS EVENT_TIMESTAMP
FROM MYLIB.EVENT_LOG;

Applications can query:

SELECT *
FROM MYLIB.EVENT_LOG_READABLE
ORDER BY EVENT_TIMESTAMP DESC;

This keeps the conversion logic in one place.

Null behavior

When the input value is null, the result is null.

VALUES FROM_SYSTEM_TIMESTAMP(
    CAST(NULL AS CHAR(8))
);

Applications should decide whether a null means:

• the timestamp was not supplied
• the event has not occurred
• the source interface did not return a value
• the data is incomplete

Do not automatically replace a null timestamp with the current timestamp. That would hide the difference between missing data and an event that occurred now.

Invalid values

The function expects a valid IBM i internal system timestamp.

Random eight-byte data is not a substitute for a timestamp value.

When converting values from an API, system table, or application file:

• preserve the full eight bytes
• avoid character conversion
• use a binary-compatible host variable or column
• confirm the source documentation identifies the field as an internal system timestamp

A CCSID conversion can alter the bytes and make the value unusable.

SQLRPGLE example

An RPG host variable receiving the raw value should preserve all eight bytes.

dcl-s InternalTs char(8);
dcl-s ReadableTs timestamp;

exec sql
   values FROM_SYSTEM_TIMESTAMP(:InternalTs)
      into :ReadableTs;

If the source value comes from an API data structure, verify that the RPG field overlays the correct eight-byte location and is not treated as text that should be converted.

Round-trip test

The companion function is:

TO_SYSTEM_TIMESTAMP

A useful test is:

VALUES FROM_SYSTEM_TIMESTAMP(
    TO_SYSTEM_TIMESTAMP(
        TIMESTAMP('2026-01-05-11.52.04.417012')
    )
);

The result should represent the original SQL timestamp within the supported precision and range.

The round trip is useful during testing, but application logic should not convert back and forth unnecessarily.

Where internal timestamps appear

An internal system timestamp may be encountered in:

• IBM i APIs
• system-generated binary structures
• service-program interfaces
• legacy application files
• diagnostic data
• system configuration data

The exact meaning of each field still comes from the interface that supplied it.

FROM_SYSTEM_TIMESTAMP converts the representation. It does not explain what event the timestamp represents.

Release and PTF requirement

IBM lists FROM_SYSTEM_TIMESTAMP as a Db2 for i functional enhancement delivered with:

IBM i 7.6 — Db2 Group PTF SF99960 Level 3
IBM i 7.5 — Db2 Group PTF SF99950 Level 12

Confirm the installed Db2 Group PTF level before using the function.

Native processing versus SQL

Use application code when:

• the value must be interpreted before SQL is available
• a low-level API structure is already being processed in RPG or C
• conversion is part of a non-SQL interface

Use FROM_SYSTEM_TIMESTAMP when:

• the raw value is stored in a table
• the value is returned to SQL
• filtering and sorting are required
• the result must join with other timestamp data
• the conversion should be centralized in a view

A practical workflow

1. Confirm the source field is an eight-byte internal system timestamp.
2. Preserve the raw bytes without CCSID conversion.
3. Display HEX(raw-value) during initial testing.
4. Convert with FROM_SYSTEM_TIMESTAMP.
5. Compare the result with a known event or source.
6. Store or expose the converted value as TIMESTAMP.
7. Format it only for presentation.

Final takeaway

FROM_SYSTEM_TIMESTAMP turns an IBM i internal timestamp into an ordinary SQL timestamp.

The conversion is small, but it removes custom byte-level timestamp logic from SQL applications and makes system data easier to query, compare, and explain.