How to get the link creation date between issues in JIRA?

[Michał Gawlik]

Hello,

When I link one issue with another by any relation (e.g. "Is Blocked by")

select * from ISSUELINK where SOURCE = 11246;
ID LINKTYPE SOURCE DESTINATION SEQUENCE
10701 10000 11246 11015 null

select * from changegroup where issueid = 11246;
ID ISSUE ID Author Created
11588 11246 admin 15/01/27

but I cannot see the relation between ISSULINK and CHANGEGROUP tables.

Does anybody know where to find it?

Thanks

Michal

[Michał Gawlik · Accepted solution]

The solution below:

SELECT lnk.id AS link_id,
       lnktyp.linkname AS link_type,
       par.id AS parent_id,
       chi.id AS child_id,
       hst.created
  FROM jiraissue chi,
       jiraissue par,
       issuelink lnk,
       issuelinktype lnktyp,
       (SELECT issueid,
               issuenum,
               MAX(created) AS created
          FROM (SELECT grp.issueid,
                       grp.created,
                       SUBSTR(to_char(it.newvalue), instr(to_char(it.newvalue), '-') + 1) issuenum
                  FROM changegroup grp,
                       changeitem  it
                 WHERE grp.id = it.groupid
                   AND it.field = 'Link')
         WHERE issuenum IS NOT NULL
         GROUP BY issueid,
                  issuenum) hst
WHERE chi.id     = lnk.source
   AND par.id    = lnk.destination
   AND lnktyp.id = lnk.linktype
   AND hst.issueid  = chi.id
   AND hst.issuenum = par.issuenum;

[Boris Berenberg]

A changegroup consists of entires in the changeitem table. I would look to see if it is in there.  

[Michal Gawlik]

Thanks for the answer!