Question
Is it okay to truncate milliseconds from timestamps when migrating from Pega 7.2.1 on Postgres to Pega 7.2.1 on Oracle?
I am migrating from Pega 7.2.1 on postgres to Pega 7.2.1 on Oracle. For some base Pega tables, such as case history tables, the data types are different and create a potential problem. Because these are parts of the Pega platform, not custom elements from my application, I want to take direction on this from Pega. I don't want to make a decision that breaks an essential part of the Platform.
For instance,
pc_history_work.pxcreatedatetime
- in Postgres, the column is of time timestamp, with 6 digits of millisecond precision in the data. Example: 2016-03-30 13:38:07.641238
- in Oracle, the column is of time Date
pc_history_work.pxsavedatetime
- in Postgres, the column is of time timestamp, with 3 digits of millisecond precision in the data. Example: 2016-03-30 13:38:07.545
- in Oracle, the column is of time Date
On Oracle, the Date type will not accept the milliseconds. Which is the recommended practice:
- truncate the milliseconds?
- change the column type?
- something else?
The attached .txt file is a sample of date, exported from Postgres, in .csv format. This website won't allow me to post .csv, so I changed the extension.
Cheers,
Elliott
What tool did you use to create the schema in the Oracle database?
Pega supports the Oracle TIMESTAMP database column as of 7.1.8. The difference between a DATE column and a TIMESTAMP column is timestamp will include the millisecond precision. If you don't need the millisecond precision in the exposed columns you can leave them as DATE.
Thanks, Celeste. I didn't know about the support for TIMESTAMP.
The crux of my question is not whether I need the millisecond precision. I need it if Pega needs it. If Pega doesn't need it, I'm happy to ignore it. Does that make sense? I am happiest when Pega works the same across various DBs, app servers, etc. Any time I see a difference between how Pega works on this stack or another, I wonder-
- is the difference something I can ignore?
- am I doomed by my ignorance?
The fact that Pega stores the data, by default, in an Oracle DATE, suggests to me that Pega can live without the milliseconds. Can you confirm that for me? It would ease my mind and allow me to ease the mind of a DBA. Easing stress for people in support positions (such as yourself) is always high on my priority list.
Accepted Solution
This article should answer most of your questions about Oracle date and timestamp columns.
https://pdn.pega.com/datetime-property-mapping-oracle-pega-7
While Pega will continue to work with Oracle DATE columns I would recommend that you update to TIMESTAMP.
Hi,
Are you facing any issues while migrating data from Postgress to Oracle database ?, If you are not facing the issue please ignore this milliseconds. Oracle database stores without this.