Historical query results & date by which items entered scope (or fixVersion)

Question

In JIRA, it is possible to ask JQL queries based on the current values of fields, such as date created, date modified, etc. With the ScriptRunner plugin, even more fun date-based queries are possible.

However, is there a way to obtain, for a given query AND a given date, the result of that query as if run on that date? JIRA keeps full history of changes, so in principle that should be possible, but after perusing all documentation for JIRA and Scriptrunner, I haven't found a way to build such a query.

The immediate application for me is to construct release burndowns for arbitrary scopes (which are represented as a query). A good proxy for what I want would be also to find out by which date an item was assigned to a fixVersion (thus entering the scope).

I suppose more approaches could be considered - if not JQL, then maybe querying the REST interface? Or is the only method to query the JIRA DB itself?

I will be grateful for any help from experienced JIRA power-users.

Answer 1

In case you want to search for all the issues updated after 9AM previous day until today at 9AM, please try:

updated >= startOfDay(-15h) and updated <= startOfDay(9h). (explanation: 9AM - 24h/day = -15h)

For reporting purposes there is a plugin which generates the following reports:

Issue Values Snapshots by Date

This report is a combination of the Sum Numeric Field by Date report and the Issue Values Snapshots by Date reports. This report sums the historical value of a specific field, based on the values that the field had at specific points in history, sampled on the first day of each interval in the selected time period. The results are filtered to include only those issues whose statuses were in one of the selected Statuses to Include values on each sampled date.

Issue Values by Date Report

This report shows you the state of your issues over time. Unlike the Issue Values Snapshots by Date Report, this report evaluates the state of your issues at all points in time over an entire report interval, rather than picking the issue state at a specific point in time. This allows you to quickly find and report on issues that had issue values for only a short period in time.

References

• Advanced Searching

• JIRA JQL searching by date - is there a way of getting Today() (Date) instead of Now() (DateTime)

• Is there a SIMPLE way to create a report that shows the number of tickets created per month for a specified time period?

Answer 2

I found a way to partially get what I want. Using the operator WAS... ON..., I can query based on historical values of some fields. This is an example query:

project = "CNT"  AND fixVersion WAS IN (18.2) ON "2018-02-01 23:59"

But there are 2 severe limitations:

• Not all fields support the historical search WAS...ON operator, in fact, it looks like a minority of the available fields support this operator
• I cannot display the values (rather than use them as a query condition) on a given day. So I cannot get a query showing me e.g. what was the Story Point value of items on a given day - they may have since been resized.