Date time search for time

[James Wolff]

I have a project where we collect a date/time field for events.  I am wanting to pull a subset of the project based on the day of the week between certain times of the day without having to change the query string each time.

The following will show me everything that happens between Tuesday 12:00 AM PST and Thursday 11:59 PM PST.

project = "XYZ" AND "Start Time (PST)" >= startOfWeek(2d) AND "Start Time (PST)" <= startOfWeek(5d) AND status != Canceled ORDER BY "Start Time (PST)"

I want to get granular and show only things that happen between Tuesday 11:00 AM PST and Thursday 10:59 AM PST

[Payne · Accepted solution]

You can use hours rather than days, like this:

"Start Time (PST)" >= startOfWeek(59h) and "Start Time (PST)" < startOfWeek(107h)

[Andrew Laden]

That will only work for the current week.

You can use Power scripts for Jira to make this more generic

https://marketplace.atlassian.com/apps/43318/power-scripts-jira-script-automation?hosting=server&tab=overview

key in silJQLExpression("(dayOfWeek(created) == \"Tue\") or (dayOfWeek(created) == \"Wed\") or  (dayOfWeek(created) == \"Thu\")", "project = SD ")

Read the documentation on silJQLExpression. There are some caveats needed to make it work will. In particular, you want to "pre-filter" your query as much as possible first to reduce runtime and load. in the example above the "Project = SD" But you can use any valid JQL in the 2nd parameter to filter down your results as much as possible before applying sil conditions in the first parameter.