Issue Status Transitions Timestamp from Database in JIRA is located in which table of the database?

[David Wilson]

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?

[Nic Brough -Adaptavist-]

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.

[David Wilson]

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?

[Matt Doar]

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)

[Nic Brough -Adaptavist-]

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.

[Matt Doar]

Not often I beat you to a post :)

Good to know the DB schema hasn't changed there. 

[David Wilson]

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?

[Matt Doar]

Which database are you using? It may be that the datetime is not being shown in full, even though it is in the db

[Nic Brough -Adaptavist-]

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?