JQL > Is it possible to query "as of" a specific date?

[Slick Rick]

Is there some sort of operator that allows you query as if you were querying on a particular day? I.e. so that you could see what the values were as of that date?

project = X AND asof = '1/1/2020'

[Mauro Calabria · Accepted solution]

Hi @Slick Rick

I'm looking for the same functionality. I just reported this as a suggestion here: https://jira.atlassian.com/browse/JRASERVER-71404

Feel free to comment and add your vote

[Slick Rick]

Thanks @Mauro Calabria ! I voted for it!

Microsoft TFS aka Azure DevOps Server has an @asof operator, and it is super valuable. If we can get that in JIRA, it opens up a world of possibility from an analytical perspective (vs. operational).

[Nicolas Courtin]

Hello @Slick Rick

I also voted for it; it is very useful to guaranty the coherence of larges queries that are split in batches. If we have a change between two batches we might retrieve twice the same record with different field values.

[Bill Sheboy]

Hi @Slick Rick  -- Welcome to the Atlassian community!

In addition to what @Sachin noted, you can use things like CHANGED for some fields (such as status) for points in time.  Please look here for more details:

https://support.atlassian.com/jira-software-cloud/docs/advanced-search-reference-jql-operators/

Best regards,

Bill

[Slick Rick]

Hi @William Sheboy ,

Thanks for the follow up! Unfortunately that won't do the trick. CHANGED only contains the most recent change.

E.g.

STORY X

CHANGED: 6/1/2020, State = In-Progress

CHANGED: 6/2/2020, State = Done

Querying for what things were as-of 6/1 wouldn't work:

changed <= '6/1/2020'

This would only get me list of things where their LAST change was 6/1.

[Bill Sheboy]

Hi @Slick Rick 

I believe CHANGED has a modifier of DURING (startDate, endDate) to allow comparisons within a time-frame.  And, as you noted earlier, this will help for things like status but not allow seeing all field at a point in time.

[Sachin]

You can use something like created > "2020/06/18" and created < "2020/06/19"
or to find all issues created in the last five days: created >= "-5d"

You can find more details about JQL here

• Find all issues created before 12th December 2010:
  created < "2010/12/12"
• Find all issues created on or before 12th December 2010:
  created <= "2010/12/13"
• Find all issues created on 12th December 2010 before 2:00pm:
created > "2010/12/12" and created < "2010/12/12 14:00" 
• Find issues created less than one day ago:
  created > "-1d"
• Find issues created in January 2011:
  created > "2011/01/01" and created < "2011/02/01"
• Find issues created on 15 January 2011:
created > "2011/01/15" and created < "2011/01/16"

[Joshua Sneed Contegix]

Hi Tariq,

Sachin looks to be correct. Check out Atlassian's Advanced search reference documentation for JQL functions. The sidebar on that page also links to the other advanced searching pages. Cheers!

[Slick Rick]

Thanks - ya I looked at the reference, didn't see what I was looking for.

For example if you wanted to get a list of User Stories and what state they were in on a given day, you've have to write a script which would dump the change history for each story, and try to derive what was the last change closest to that date and what the state was at that point in time.

Microsoft's Azure DevOps Server (formerly TFS) as a similar WIQL language, which has an asof operator so that you can do point in time queries.

Was hoping something similar existed in JQL.

[Joshua Sneed Contegix]

Hi Tariq,

Unfortunately, no. JQL is limited to what you see in those docs. You may be able to find a plugin to bring that capability to Jira in the Marketplace. Cheers!

[Slick Rick]

Thanks @Joshua Sneed Contegix 

I figured - but thought I'd pose the question in case anyone has been able to solve this, or is aware of a third party extension (I didn't see one).

[Suresh Adimulam]

@Slick Rick Did you write a separate script or built in functions? I also need a way to get my defects on a given day. thx

[Tariq Ahmed]

Hey @Suresh Adimulam ,

Unfortunately no. The approach I'm taking is working on a script which grabs totals for the day, and saves that snapshot in my own database. If I ever get enough time will be happy to publish it for anyone who wants to use it. It's very rudimentary right now (writing it in GO, as the first thing I've ever written in GO).

If it's possible to grab the change history of the status field for a given work item via the API, you could reverse engineer what everything was on a given day.