Date math

[Tim Arthur]

Having a bear of a time reconciling a couple of filters.

First filter without any date math has a total of 50.

Sum total of these 3 is 49. 

All other criteria are the same.

I've seen hard coded dates used in formulae, but I don't want that.

What am I missing? My mind is not wired for backward notation :-)

Last 48 hours: created >= -2d

3-7 days ago:  created >= -7d AND created < -2d

More than 7 days ago: created < -7d

[Tim Arthur · Accepted solution]

The three date math queries were in my first post.

Here is the full profile. As of right now, it's totaling right. Sometimes the overall aggregate "all issues" is off by one.

All issues

project = NUC and issuetype in (Bug, Story) and status not in (Canceled, Closed, Complete, "Ready for Delivery")

returns 34 bugs, 15 stories, 49 total

--

Opened in last 48 hrs.

Project = nuc and status not in (Canceled, Closed, Complete, "Ready for Delivery")  and issuetype in (bug, story) and created >= -2d

Returns 6 bugs

--

Opened 3-7 days ago.

Project = nuc and status not in (Canceled, Closed, Complete, "Ready for Delivery")  and issuetype in (bug, story) AND created >= -7d AND created < -2d

Returns 4 bugs

--

Opened more than 7 days ago.

Project = nuc and status not in (Canceled, Closed, Complete, "Ready for Delivery")  and issuetype in (bug, story) and created < -7d

Returns 24 bugs, 15 stories, 39 total

--

Thank you for your help on what I can check for or change. Again, if there is a place where date math is explained in detail, we'd all benefit.

[Nic Brough -Adaptavist-]

Ok, the dates are stored as epoch dates and times - it's a number of milliseconds since midnight on the 1st January 1970.

When you use numbers like "-7d" it's simply calculating what that is in milliseconds (1000 * 60 * 60 * 24 * 7) and subtracting that from "now".

The usual problem people see with dates is that it is being very precise when it's doing that.

Imagine you created an issue at 11am on Monday.  If you use a query that says created > -4d, it won't return that issue (because I'm typing this comment at 13:40).  The issue was created 4 days ago, but because the time is in the morning and it is now the afternoon, "now -4d" won't find it - it's more than 4 days old technically.

The fix for this is to use the functions that work with dates and times the way humans think - instead of the raw arithmetic of  "> -4d", use "> startOfDay (-4d)"

[Tim Arthur]

Solved. Thank you Nic.

[Nic Brough -Adaptavist-]

I am not sure what the question is here. 

You've given us three filters that I would expect to give you different numbers of issues given a normal data set, but not explained where the 49 or 50 counts are coming from.

The filters are looking at different things, so I'm not sure what you are doing when comparing them with each other or something else.

Could you explain a little more?  Like:

Filter	Result
Filter overall	50

Filter	Result
created >= -2d	12
created >= -7d AND created < -2d	22
created < -7d	15
	49

[Tim Arthur]

Nic - thanks for your reply. Didn't see this one come through by email. Question is why would the sum of those three queries produce a different total than a query that does not look at "when created". I have a rather simple query that looks at open bugs for a given project. Total without date queries is 50. Yet, when I run each of those 3, they add to only 49. I am trying to learn more about data math and can't seem to find anything. Something is slightly amiss.

[Nic Brough -Adaptavist-]

It's because the queries are different, and do not refer to the same things.

Could you give us the four filters I asked for?