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 |
Accepted answer
It looks to be the Java milliseconds. If true, the three values in the original post can be converted to "date" as
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
Ralph Schoon selected this answer as the correct answer
Comments You can find the types in the API. Most are based on the Java Types Duration/long and Timestamp. I agree with Donald, use the reporting solutions or the API to access that data.
Donna Thomas
commented Apr 30 '15, 10:55 a.m.
We'd love to - we opened a PMR for this type of information, and got nothing for a week, other than here-is-super-basic-info and pay-for-service-then-we'll-help. We have been completely UNIMPRESSED with IBM on reporting support. So due to time constraints to get the information out of the database, we are doing what we have to do to get it done.
Ralph Schoon
commented Apr 30 '15, 11:02 a.m.
| edited Apr 30 '15, 11:03 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER
Donna, I have access code and value to text/Text to value transformation code published here for pretty much all attribute types: https://rsjazz.wordpress.com/2015/02/27/a-rtc-workitem-command-line-version-2-2/ the code comes with source code. You might want to look into it, if you are not sure about the values. The code you look for is in the WorkItemHelper.
|
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 |
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.