Ticket open duration in days

[Krzysztof Achinger]

Hello EAP Team,

I am trying to show a table with tickets' open duration in days. The column should show days between Issue Created At date and now. Is it possible, is there an easier way to achieve that?

I was looking to add a column that stores now date but that did not seem to be possible.

[Tracy Chow · Accepted solution]

You can add a formula column with a custom formula using the SQLite datediff function and the relative date variable {TODAY}. 

[Krzysztof Achinger]

It did the trick. I was actually playing with {TODAY} after reading the documentation but I did not know I need to use Custom formula for it to work. Thank you.

[Kamal Rajpal]

Is there any way to get this calculation done in JQL, which I am using to create filter in JIRA

[Cat Africa]

Hi, @Kamal Rajpal! It may be better to ask this question in Jira's Community group: https://community.atlassian.com/t5/Jira/ct-p/jira

[Cat Africa]

Hey, Krzysztof! You can use the {TODAY} relative date variable to get today’s date.

For what you’re trying to do, you can do the following (assuming you’ve made your initial query with the "Created At" column):

1. Add a Formula column step.

2. Select Custom as the formula type.

3. Enter {TODAY} as the formula.

4. Select Save. You should now have a new column where all the rows are today’s date.

5. Add another Formula column step.

6. Select Date difference as the formula type.

7. For the parameters, first select the Created At column, then select the new today column you created, then select Day as the time unit.

8. Select Save. This should give you the date difference you’re looking for.

Hope that helps a bit!

[Krzysztof Achinger]

It did help, thank you.

[Adam Nichols (DISH)]

We have run into the same issue and had to create a custom script

[Krzysztof Achinger]

No way! How do you create custom script and where do you store the data if that is not too much to ask? Any additional pointers will help.

[Adam Nichols (DISH)]

Here is the script we used in Automation for Jira

{{now.diff(issue.created).abs.days}}

[Krzysztof Achinger]

Right, all my previous data issues I was also resolving with Automation for Jira. I was hoping that I will not have to do it in Atlassian Analytics.

@Matt David @Cat Africa hope you are reading :)

[Adam Nichols (DISH)]

Actually this is for our Jira Software, not Analytics, so that actually may be a different solution, I will defer to EAP

[Vinci Louise Morales]

Is there a way to change the format of {TODAY} into Date and Time?

[Rohit Kavuluru]

In Atlassian Analytics, you can't change the {TODAY} format as it's a pre-defined system variable representing the current date. There isn't a built-in feature to change its format. 

You can, however, add an additional"Created At" column below to display the date and time separately in your results table. You can also manually query for the date or time and display them as separate columns using our SQL editor. Hopefully, this addresses your question!