How can I read a date value from the CCM database?
In looking at the data in the SQL Server database for CCM, I can see a field indicating the Modified Date/Time (called just MODIFIED). Using this select statement, I can display the latest value.
SELECT p.NAME, MAX(w.MODIFIED)
FROM CCM.MODEL.WORK_ITEM w, CCM.PROCESS.PROCESS_AREA p
Where w.CONTEXT_ID = p.CONTEXT_ID
Group by p.NAME
Returns max values such as
1393640590433
1425492026982
1338577294446
How is the number that is returned for MODIFIED, converted into a date? It doesn't appear to be a date from the database point of view (doesn't recognize it as a date). And even trying with MS-Excel, it doesn't convert to a date.
Help please!
Thank you much,
Donna
SELECT p.NAME, MAX(w.MODIFIED)
FROM CCM.MODEL.WORK_ITEM w, CCM.PROCESS.PROCESS_AREA p
Where w.CONTEXT_ID = p.CONTEXT_ID
Group by p.NAME
Returns max values such as
1393640590433
1425492026982
1338577294446
How is the number that is returned for MODIFIED, converted into a date? It doesn't appear to be a date from the database point of view (doesn't recognize it as a date). And even trying with MS-Excel, it doesn't convert to a date.
Help please!
Thank you much,
Donna
Accepted answer
It looks to be the Java milliseconds. If true, the three values in the original post can be converted to "date" as
Saturday, 1 March 2014 02:23:10 GMT
Wednesday, 4 March 2015 18:00:26 GMT
Friday, 1 June 2012 19:01:34 GMT
But as Alan said, it's not a good idea to access data directly from the database. It's likely you are going to put the "modified date" in a report. If so, consider using BIRT report and/or the reportable REST API.
http://www.ibm.com/developerworks/rational/library/create-custom-reports-birt-rtc/
https://jazz.net/wiki/bin/view/Main/ReportsRESTAPI
Comments
One other answer
A "best guess" would be that it's some type of internal date format used by the underlying Java, Javascript etc. within the product.
Please be aware though that it's not normally a good idea to access the RTC database directly as the attempts could lead to resource contention which may impact performance of the RTC server.
As the schema of the database in not published as a programming interface there's also a real danger that anything you design to extract data from the database could easily be invalidated if those data are changed, moved, removed or deprecated during the development of the product and throughout it's maintenance cycle.
If you're trying to extract data from RTC you are far better served by accessing data from RTC via the published APIs
Please be aware though that it's not normally a good idea to access the RTC database directly as the attempts could lead to resource contention which may impact performance of the RTC server.
As the schema of the database in not published as a programming interface there's also a real danger that anything you design to extract data from the database could easily be invalidated if those data are changed, moved, removed or deprecated during the development of the product and throughout it's maintenance cycle.
If you're trying to extract data from RTC you are far better served by accessing data from RTC via the published APIs