How to write a JQL query to compare a JPD date?

Glenn A
Contributor
October 31, 2024

Hi!

OK, so, I have issues in JPD with the project due date set. This 'date' is actually a string of a JSON object with a start and end date. The following allows me to consistently extract the actual date well enough for use in automations:

issue.customfield_12345.substring(29,39)

Now, I want to create a JQL query to give me the set of issues with a due date this coming month. JQL doesn't seem to like that string enough to use it in any number of typical JQL date or comparison functions. I've tried converting from string to date too... maybe incorrectly?

Any thought on the JQL that would do this... conceptually along the lines of:

project IN ("Project") AND '{{issue.customfield_12345.substring(29,39)}}' < endOfMonth()

Actually, rather than overthinking it, how can I write a JQL query to compare a JPD date field to a date (like today)?

Thoughts?

Thanks!

2 answers

1 vote
Eric Sibomana
Contributor
October 31, 2024

Hello Glenn,

Regarding your question about comparing a JPD date field to a date using JQL, I understand the complexity of your situation. Unfortunately, JQL doesn't support direct string manipulation or JSON parsing within queries, making comparing dates stored in JSON format challenging.

However, I can suggest a few potential workarounds:

  1. Custom Field Optimizer: Consider using a Custom Field Optimizer app to create a new custom field that extracts and stores the data in a proper date format. This would allow you to use standard JQL date comparisons.
  2. Scripted JQL Function: If you have ScriptRunner for Jira installed, you could create a custom scripted JQL function to parse the JSON and extract the date for comparison.
  3. Automation Rule: Create an automation rule that extracts the date from the JSON and updates a separate date custom field. You can then use this field in your JQL queries.
  4. Search Extension: Implement a search extension that allows for custom field parsing and comparison logic.

For your specific use case of finding issues due in the coming month, once you have the date in a proper format, you could use a JQL query like this:

project IN ("Project") AND customfield_DATE >= startOfMonth() AND customfield_DATE <= endOfMonth()

Where customfield_DATE is your extracted and properly formatted date field. 

I recommend exploring the Custom Field Optimizer or automation rule options first, as they're generally easier to implement and maintain.

Don't hesitate to reach out if you need further clarification or assistance!

Best regards,
Eric

Glenn A
Contributor
October 31, 2024

Thanks @Eric Sibomana, will dig into your suggestions! I was starting down the path of the automation to pull the substring into a new field, but it would need to be a string field (as JPD's date is 'compromised') so back at square one with that approach if I am unable to leverage it in JQL.

0 votes
Hannes Obweger - JXL for Jira
Atlassian Partner
November 1, 2024

Hi @Glenn A

If you're open to solutions from the Atlassian Marketplace, you may want to have a look at the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets.

It also fully supports JPD date fields, like so:

jpd-date-range.gif

As you can see above, you can easily sort and filter by JPD date range fields, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, conditional formatting, or bulk editing via copy/paste.

Once you've narrowed down your list of issues, you can work on your issues directly in JXL, trigger various operations in Jira, or export your issues with just one click.

This all just works - there's no scripting or automation whatsoever required.

Any questions just let me know,

Best,

Hannes

Glenn A
Contributor
November 1, 2024

thanks @Hannes Obweger - JXL for Jira will give it a look!

Glenn A
Contributor
November 1, 2024

@Hannes Obweger - JXL for Jira quick question: can I embed (and manipulate/craft) a JXL 'object' in a confluence page? (essentially emulate automated Jira macros)

Hannes Obweger - JXL for Jira
Atlassian Partner
November 1, 2024

Hi @Glenn A,

yes, you can embed JXL sheets into Confluence - see our documentation here for further details.

Hope this helps!

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
TAGS
AUG Leaders

Atlassian Community Events