JIRA DB query to get custom fields of all issues on a given day

Question

Regarding https://confluence.atlassian.com/display/JIRA040/Issue+status+and+workflow, I understand we can query status of all issues on a specific day.

Do we have a query for other fields such as summary or custom fields?

For example: There are 2 change items on 07/15/2018.

Field       oldstring     newstring

A             A1               A2

B             B1               B2

If I would like a query to get values on 07/14/2018, it should return:

A         A1

B         B1

If I run with the date 07/15/2018 and afterward, it should return:

A         A2

B         B2

Earl McCutcheon · Answer

Hi Khiết,

OK Got ya, and in that case you need to look at the changehistory and the changeitem tables, and we have a KB with some example queries for this including one for a specific custom field, viewable here:

* https://confluence.atlassian.com/jirakb/how-to-obtain-issue-change-history-from-database-933695139.html

Regards,
Earl

khietbt2 · Answer

Thanks, @Earl McCutcheon. However, your query only returns the latest value of TEST.

I am looking for a query giving the value of a given date in the past. For example: Last week.

For example: There are 2 change items on 07/15/2018.

Field       oldstring     newstring

A             A1               A2

B             B1               B2

If I would like a query to get values on 07/14/2018, it should return:

A         A1

B         B1

If I run with the date 07/15/2018 and afterward, it should return:

A         A2

B         B2

Earl McCutcheon · Answer

Hello Khiết,

You would want to look at the textvalue column on the customfieldvalue database table, for a custom field and the Summary is located in the jiraissue table.  Additional database table details and the schema can be seen in

* https://developer.atlassian.com/server/jira/platform/database-schema/

For an exe query to get the value of a custom field named TEST on an issue TST-1 you could use this query (Noting Syntax is PostgreSQL):

select v.textvalue from customfieldvalue v where customfield = (select f.id from customfield f where f.cfname = 'TEST') and v.issue = (select i.id from jiraissue i where i.project = (select p.id from project p where p.pkey = 'TST') and i.issuenum = 1);

textvalue

-----------

Test

(1 row)

Regards,
Earl