Used the following script: select id: SELECT jira.changeitem.oldstring, jira.changeitem.newstring, jira.changegroup.issueid, jira.changegroup.author, jira.changegroup.created, jira.jiraissue.id, jira.jiraissue.issuestatus, jira.jiraissue.updated, jira.jiraaction.updateauthor
FROM jira.changeitem
JOIN jira.changegroup ON changeitem.groupid=changegroup.id
JOIN JIRA.JIRAISSUE ON changegroup.issueid=JIRAISSUE.id
JOIN JIRA.JIRAACTION ON changegroup.issueid=JIRAACTION.issueid
WHERE jira.changeitem.field='status' AND JIRA.CHANGEGROUP.created >='01-JAN-18' AND JIRA.JIRAISSUE.PROJECT = '17061';;
When the resolves appear the date appears as day-month-year, no timestamp appears. In JIRA the status transitions on Transitions tab displays the time in Status. Where in the JIRA database is the table that contains the Status Transitions Timestamp?
Changeitem stores the line (status changed to status, on one row, with any other changes made in the transition in their own rows). Changegroup stores the date and time of the change.
Thanks for your responds. In the JIRA database, which we have, the Changegroup table only contains the date without a timestamp, The column is titled simply 'Date'. Is there someplace else this information is stored?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
A single changegroup can have multiple changeitems in it. I see a datetime field named created in changegroup with Jira 7.2.10 and MySQL
I see datetime in the data:
mysql> SELECT changeitem.oldstring, changeitem.newstring, changegroup.issueid, changegroup.created
-> FROM changeitem
-> JOIN changegroup ON changeitem.groupid=changegroup.id
-> JOIN jiraissue ON changegroup.issueid=jiraissue.id
-> JOIN jiraaction ON changegroup.issueid=jiraaction.issueid
-> WHERE changeitem.field='status' AND jiraissue.project = 10226 and jiraissue.id=6647420;
+-----------+-----------+---------+---------------------+
| oldstring | newstring | issueid | created |
+-----------+-----------+---------+---------------------+
| Open | Closed | 6647420 | 2018-03-09 17:59:51 |
+-----------+-----------+---------+---------------------+
1 row in set (0.01 sec)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I was just about to post much the same. Changegroup in Jira 6.4, 7.1, 7.2, and 7.8 looks exactly like that, with a "created" date/time stamp, not "Date" without a time.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks again for your responses. In the JIRA database (JIRA 7.5.3), which we have, the Changegroup table only contains the date without a timestamp, see below
Oldstring Newstring Issuenum Created Status Updated
To Do In Progress 1687 24-JUN-16 6 10-AUG-17
Verified Closed 1687 12-JUL-17 6 10-AUG-17
In Progress Done 1687 09-AUG-16 6 10-AUG-17
In Review Done 1687 08-AUG-16 6 10-AUG-17
The column is titled simply 'Date'. Is there someplace else this information is stored?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
No, and there is no column "date" in your output, there's created and updated. Could you try the query Matt gave instead, so we can see the actual table?
It also looks to me like whatever you are doing this SQL with is dropping the time from the "created" display as well. From memory, there's a couple of MS-SQL tools that you have to bludgeon to display times, could it be that?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.