DateTime properties in Pega 7 can be mapped to both DATE and TIMESTAMP columns in an Oracle database. Report definitions, List Views, and Summary Views all support these column types.

Recommended use

Both column types display similar numerical data for DateTime properties when viewed in the Report Definition. However, it is recommended that you use TIMESTAMP columns whenever possible because of the increased precision provided by millisecond data that DATE columns do not provide.

The following example shows the clipboard result page that is included in the report for a TIMESTAMP column:

Upgrading from previous releases

When you upgrade from a Pega 7 version that is earlier than Pega 7.1.8, any existing DateTime properties continue to map to the DATE column. These can be manuallychanged to map instead to TIMESTAMP columns, and are maintained as TIMESTAMP columns for any upgrades to future releases of Pega 7.

Any new DateTime properties created post-upgrade by using the following criteria are mapped to TIMESTAMP columns:

• Optimizing a DateTime property for reporting
• Exposing a column from the "Modify schema" landing page
• Creating a new class with a dedicated table
• Importing a RAP with a TIMESTAMP column

Converting an existing column

To convert an existing DATE column to TIMESTAMP, the ALTER script has to be executed on the database manually. Data is unaffected by the conversion.

Connect to the Pega 7 database and run the following ALTER script and update the Schema_Name, Table_Name, and Column_Name values, which are highlighted below.

ALTER TABLE <Schema_Name>.<Table_Name> MODIFY <Column_Name> TIMESTAMP

For example, pc_History_Work is an internal table with the column pxTimeCreated of the type DATE. To convert the pxTimeCreated column to instead use TIMESTAMP for the column, run the following script:

ALTER TABLE <Schema_Name>.pc_History_Work MODIFY pxTimeCreated TIMESTAMP

Additional resources

Oracle knowledgebase article – oracle.sql Class TIMESTAMP