Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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'

 

 

 

3 answers

1 accepted

0 votes
Answer accepted

Hi @Tariq Ahmed

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

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).

Hi @Tariq Ahmed  -- 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

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.

Hi @Tariq Ahmed 

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.

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"

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!

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.

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!

Like Tariq Ahmed likes this

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).

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

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. 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
FREE
PERMISSIONS LEVEL
Site Admin
TAGS
Community showcase
Published in Jira

Announcing the waitlist for Jira Work Management

Hey there Cloud Community members! We’re excited to give you the first glimpse of the new home for business teams on Jira — Jira Work Management. Jira Work Management is the next generation of J...

874 views 14 20
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you