JIRA JQL searching by date - is there a way of getting Today() (Date) instead of Now() (DateTime)

Question

I am trying to create some Issue Filters in JIRA based on CreateDate.

The only date/time function I can find is Now() and searches relative to that, i.e. "-1d", "-4d" etc.

The only problem with this is that Now() is time specific so there is no way of getting a particular day's created issues.

i.e. Created < Now() AND Created >= "-1d"

when run at 2pm today will show all issues created from 2pm yesterday to 2pm today
when run at 9am tomorrow will show all issues created from 9am today to 9am tomorrow

What I want is to be able to search for all issues created from 00:00 to 23:59 on any day. Is this possible?

Promethium

Answer 1

Check out startOfDay([offset]). That gets what you are looking for without the pesky time constraints and its built in as of 4.3.x. It also has variants like endOfDay, startOfWeek, startOfMonth, etc.

Horsetail

We're still on 4.2.something so can't check this out yet :( upgrade is planned but it's such a PITA to do... – Promethium

We have finally upgraded and can now confirm this answer! Apologies for the delay :) – Promethium

Do you know how one could combine arguments to get something like 'all the issues updated after 9am yesterday' ? I tried something like startOfDay(-1d+9h) but that is not accepted. – Arrangement

startOfDay reference – Waive

Answer 2

I run it like this -

created > startOfDay(-0d)

It gives me all issues created today. When you change -0d to -1d, it will give you all issues created yesterday and today.

Retarded

Answer 3

We're using Jira 6.2 and I use this query:

updatedDate > startOfDay(-1d) AND updatedDate < endOfDay(-1)

to return all of the issues that were updated from the previous day. You can combine with whichever queries you want to return the appropriate issues for the previous day.

Gassing

Answer 4

Just for the sake of keeping the information up-to-date, with at least JIRA 7.3.0 (maybe older as well) you can explicitly specify the date in multiple formats:

• 'yyyy/MM/dd HH:mm';
• 'yyyy-MM-dd HH:mm';
• 'yyyy/MM/dd';
• 'yyyy-MM-dd';
• period format, e.g. '-5d', '4w 2d'.

Example:

updatedDate > '2018/06/09 0:00' and updatedDate < '2018/06/10 15:00'

Hornbook

Answer 5

In case you want to search for all the issues updated after 9am previous day until today at 9AM, please try: updated >= startOfDay(-15h) and updated <= startOfDay(9h). (explanation: 9AM - 24h/day = -15h)

You can also use updated >= startOfDay(-900m) . where 900m = 15h*60m

Reference: https://confluence.atlassian.com/display/JIRA/Advanced+Searching

Dinar

Please re-read the question! Your answer does not relate to the question asked. – Promethium

I think eliberator was trying to respond to a question asked in the accepted answer. – Jeremiah

This is still useful information regarding question in general. – Norrie

Answer 6

A friend who is a JIRA wiz showed me that you can actually pass the filter (escaped) as a jqlQuery parameter to JIRA via URL:

http://hostname/secure/IssueNavigator!executeAdvanced.jspa?clear=true&runQuery=true&jqlQuery=created%3E='2010-05-31%2000:00'%20AND%20created%3C='2010-06-06%2023:59'%20ORDER%20BY%20created%20ASC

I created an ASP.Net page which generates the URLs based on an offset week or month.

Everybody's happy!

Promethium

Answer 7

You would expect that this is easily possible but that seems not be the case. The only way I see at the moment is to create a user defined JQL function. I never tried this but here is a plug-in:

http://confluence.atlassian.com/display/DEVNET/Plugin+Tutorial+-+Adding+a+JQL+Function+to+JIRA

Amethyst

having loads of trouble getting the MAVEN plugin to install in Eclipse :( – Promethium

I have not much experience with eclipse, I usually use netbeans. Though I did not try much or anything with maven yet, I think it should be easy if you install the full netbeans installer... – Amethyst

Answer 8

You might use one of our plugins: the JQL enhancement functions - check out https://plugins.atlassian.com/plugin/details/22514

There is no interval on day, but we might add it in a next iteration, if you think it is usefull.

Francis.

Leigh

Martens - that looks like exactly what we need, it's actually week calculations that we need to perform although if you could add a Day interval that would be great. I've passed it to our JIRA admin and we'll test it. I'll let you know how we get on. – Promethium

is the plugin time zone aware? I've tested it with the week interval and it seems to be returning dates on Mondays with a time of 12:00:00.000 I checked the code in the repository and it should be returning 00:00:00.000 (if I read the Java correctly, I'm not a Java dev!) – Promethium

Sorry for the late answer. There is indeed a bug with the timezone calculation It has been file'd under studio.plugins.atlassian.com/browse/IJQL-7 We'll get it fixed in the course of the week (and we'll add support for day in the mix) Francis – Leigh