It's all about the answers!

Ask a question

How can I read a date value from the CCM database?


Donna Thomas (14122242) | asked Apr 29 '15, 12:04 p.m.
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


permanent link
Donald Nong (14.4k314) | answered Apr 29 '15, 11:22 p.m.
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
Ralph Schoon selected this answer as the correct answer

Comments
Ralph Schoon commented Apr 30 '15, 1:59 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER

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.

Good luck!

One other answer



permanent link
Alan Sampson (93729) | answered Apr 29 '15, 12:46 p.m.
JAZZ DEVELOPER
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


Register or to post your answer.