Date/time based ticket queries

[trac@…]

When selecting options to create a custom query the option to select by date is not available. It'd be nice if tickets could be filtered by creation and/or closure date.

[cmlenz]

Yeah, that'd be really nice.

[garyo]

We are doing this now with 0.9b2, but it is a little painful. We use the sqlite date/time functions to process the times into dates. Here's our Tickets closed recently report:

select id, summary, component, milestone, owner, 
  (select max(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed')
   as closetime,
  datetime((select max(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed'), 
            'unixepoch', 'localtime') as closedate
  from ticket t
where status = 'closed'  and closetime > strftime('%s',datetime("now","-2 days"))+0
 order by closetime;

Perhaps that will help someone...

[dmlee@…]

I do a similar report to show a list of recently modified tickets. We'd like to transition to just using the Query module, but this is a pretty popular report.

SELECT 
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
      ELSE 
        (CASE owner WHEN '$USER' THEN 'font-weight: bold' END)
    END) AS __style__,
   e3.value AS __color__,
   status AS __group__, 
   id AS ticket, 
   priority, 
   t.type AS type, severity, 
   time as created, 
   changetime as modified, 
   reporter,
   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
   summary 
  FROM ticket AS t, enum AS e, enum AS e2, enum AS e3
  WHERE 
   e.name = t.priority AND
   e.type = 'priority' AND
   e2.name = t.status AND
   e2.type = 'status' AND
   e3.name = t.severity AND
   e3.type = 'severity' AND
   (strftime('%%s','now') - changetime) < (7 * 24 * 3600)
  ORDER BY e2.value, resolution, -changetime

[telenieko at telenieko dot com]

subscribing myself to that ticket.

[cboos]

Tickets #2849 and #2957 were marked as duplicate.

[trac@…]

I want notifications for this ticket. Hopefully Automattic's SPAM detection behaves now with a comment?

[cmlenz]

#3990 has been marked as duplicate of this ticket.

[cboos]

Well, #3990 was about the TicketQuery macro. I think it makes sense to start by this, so later we can concentrate on the UI aspect.

[cboos]

#4957 was closed as duplicate. It presents some use cases for a last modified since criterion.

About the milestone, I'm not sure this can make it for 0.11 ...

[joel]

when this is implemented, it will be great if you can query by creation or change date to include being able to query within a start and end date (ie. change date >= X and change date <= Y

[ecarter]

Ugly implementation attempt, incomplete.

[ecarter]

The attached patch doesn't do everything I'd want, has lots of warts, (and the warts have warts,) etc. I attached it mostly just to show (some of) the places that will need to be modified. I probably won't mess with this for a while.

[cboos]

Why not < and > for before and after? (see #5031)

[ecarter]

Replying to cboos:

Why not < and > for before and after? (see #5031)

I was seeing them get translated to &lt; and &gt;, and didn't want to debug that until I had a better understanding of the rest of what had to be done. So I just picked a couple of unused characters, basically at random.

[earl.powers@…]

Replying to garyo:

Perhaps that will help someone...

HUGE HELP! Thanks for the tip!

[anonymous]

Adding myself to cc list

[osimons]

#6831 closed as duplicate of this ticket.

In short it calls for adding Created and Modified fields as filters and result set columns.

[cboos]

jQuery UI comes with a nice datepicker, see http://docs.jquery.com/UI/Datepicker.

[cboos]

#7349 was closed as duplicate. There's a patch on that ticket which provides the requested feature.

I've tested a bit that patch, here are a few comments:

• lots of spurious whitespace changes in trac/htdocs/query.js, please revert
• the changes below trac/ticket all look good, you've taken care of the various FIXME related to this issue
• now concerning the UI:
  • I'm probably stupid, but using the colorpicker for selecting this date range in one text input is not really intuitive (I always had to edit the date by hand in the end)
  • the datepicker colors are not really matching the Trac style

So, to sum it up, the feature works but there are usability issues. What about having something like this instead?

Created       after  ________|  and before  ________|

and use datepicker to get one date per field. The additional benefit is that leaving one field empty gives you and open ended range.

[anonymous]

Replying to cboos:

I've tested a bit that patch, here are a few comments: - now concerning the UI: - I'm probably stupid, but using the colorpicker for selecting this date range in one text input is not really intuitive (I always had to edit the date by hand in the end) - the datepicker colors are not really matching the Trac style

jQuery UI datepicker is a quick solution for inputting date range, it also guarantees after/before constraint. You can click twice on the calendar panel (first for beginning date, second for ending date) then selected region in dark bg color. The css file is shipped with jQuery UI dist, we should adapt it to Trac style, but I'm not a color master :-(

So, to sum it up, the feature works but there are usability issues. What about having something like this instead? {{{ Created after | and before | }}} and use datepicker to get one date per field. The additional benefit is that leaving one field empty gives you and open ended range.

It's clear. But should we keep after/before constraint?

[goncha@…]

Sorry, I forgot my mail address ;-p

[cboos]

Replying to anonymous:

Replying to cboos:

I've tested a bit that patch, here are a few comments: - now concerning the UI: - I'm probably stupid, but using the colorpicker for selecting this date range in one text input is not really intuitive (I always had to edit the date by hand in the end)

jQuery UI datepicker is a quick solution for inputting date range, it also guarantees after/before constraint. You can click twice on the calendar panel (first for beginning date, second for ending date) then selected region in dark bg color.

Yeah I finally figured out ;-) But still, this is not intuitive, especially when you want to select a range that spans months or years.

Created after ________| and before ________| and use datepicker to get one date per field. The additional benefit is that leaving one field empty gives you and open ended range.

It's clear. But should we keep after/before constraint?

I think it's doable, datePicker supports minDate and maxDate parameters, so the before picker can be constrained by the after date as minDate if set, and vice versa, the after date can be constrained by the before date as its maxDate. See http://ui.jquery.com/functional_demos/#ui.datepicker, look for "Another Date Range" demo (though the enforcing of min/max constraints seems broken on that demo).

[cboos]

"Another Date Range" demo (though the enforcing of min/max constraints seems broken on that demo).

... and this seems to be fixed in jQuery UI 1.5.1 (http://ui.jquery.com/bugs/changeset/290)

[andrei2102@…]

Independent from this patch I created the same functionality and and now we have it installed and working. We needed two key functionalities in the query module:

• greater and lesser comparison operators
• time and changetime fields filters

You can see here: http://code.optaros.com/trac/oforge/query?changetime=%3E07%2F08%2F08&owner=~aculapov how it works, and links to the patch and notes about it's usage can be found on: http://code.optaros.com/trac/oforge/wiki/Dashboard

My patch has bundled the two features described above, and if need it I can split it in two.

[rblank]

I'll try to take all the ideas presented here and in #7349 and to come up with a decent solution.

[rblank]

Patch against trunk adding time-based queries

[rblank]

The patch above is a basic implementation of time-based queries. It combines and extends the functionality of the patches provided by ecarter and goncha@.

• The fields time and changetime have become "standard" fields in the ticket model, with a new type "time".

• The names "created" and "modified" are defined as synonyms for "time" and "changetime", as the latter are not very intuitive. This was already the case in 0.10 for the sorting column.

• The query interface is the solution proposed by cboos with two fields for "between ... and ...". An empty field means "unconstrained". The constraint can be either "is between" or "is not between". Multiple constraints can be defined for time fields.

• Contrary to goncha's implementation, this patch doesn't add one day to the end of the range. This means that a query with the same value in both fields will return no results. This might sound confusing at first, but is actually very useful with relative time specifications.

• The patch adds some simple relative time parsing. This allows specifying e.g. "10 days ago" or "last week" in queries. For example, to get all the tickets created last month, use "last month" and "this month" as limits. Or to get all tickets that have not been active in the last 30 days, use "is not between", "30 days ago" and "" in a constraint over "changetime".

• When using a TicketQuery macro, specify the range as a single field, with an @ separating both dates, for example "modified=2008-01-01@2008-07-01".

I have not yet added jQuery datepicker controls. My first trials have not been successful, and I'm not sure this adds much value. Indeed, I have found that it is often simpler to use relative time specifications.

It would be great if this patch could receive some testing.

[osimons]

Replying to rblank:

It would be great if this patch could receive some testing.

I've tested it, and it works really well. Just a few comments:

• The date divider @ is not used for ranges elsewhere, but always used as more of a sub-specification like at a specific revision. I know that the natural option of : cannot be used due to time support (like HH:mm). However, is there some other symbol that makes more intuitive sense? Perhaps reusing the ^ (starts with) and $ (ends with) query markers - examples modified=^2008-08-29 (on or after), modified=$2008-08-29 (before), modified=^2008-08-01$2008-08-31 (between)?
• I've come to terms with natural language time output where we convert timestamps to things like '2 weeks ago'. However, with ongoing efforts of i18n this will bite us when requests arrive to add support for other languages than English. IMHO, that is a level of complexity that has no place in Trac, but perhaps one day it could be a feature in Babel that we could reuse?

[rblank]

Replying to osimons:

I've tested it, and it works really well. Just a few comments:

Thanks for testing!

* The date divider @ is not used for ranges elsewhere, but always used as more of a sub-specification like at a specific revision. I know that the natural option of : cannot be used due to time support (like HH:mm). However, is there some other symbol that makes more intuitive sense? Perhaps reusing the ^ (starts with) and $ (ends with) query markers - examples modified=^2008-08-29 (on or after), modified=$2008-08-29 (before), modified=^2008-08-01$2008-08-31 (between)?

I have avoided the existing modifiers on purpose:

• The code already treats them specially if they are present right after the =, so for example, in modified=$2008-08-29 the $ would be treated as a modifier, whereas in modified=^2008-08-01$2008-08-31 the ^ would. I don't feel like making the modifier code even more complex than it already is to manage this case.

• The "is not between" constraint already uses the modifier !, so for example modified=!$2008-08-29 would result in $ not being a modifier, contrary to the example above. Basically, mixing modifiers and separators results in a mess.

The current modifiers are !~^$, so I had to select another "special" character. Other characters to avoid are :-,/.. On my keyboard, this reasonably leaves the following set: +&=?*%[]{}@#<>;_, although I wouldn't recommend the first four. I picked @ at random (and maybe because in french, "at" can be literally translated as "to" (as in "from ... to ...")). What about ; or _? They would seem good candidates as separators.

* I've come to terms with natural language time output where we convert timestamps to things like '2 weeks ago'. However, with ongoing efforts of i18n this will bite us when requests arrive to add support for other languages than English. IMHO, that is a level of complexity that has no place in Trac, but perhaps one day it could be a feature in Babel that we could reuse?

I thought you would say that :-) The patches above had no relative time specifications, and at first I hadn't either. But two important applications of time-based queries are "show me the tickets that have been created last month", and "show me the tickets that have been inactive for 30 days". So I figured relative time specs should be allowed.

Then I thought of using something like "30d" for 30 days, but I couldn't find a good way to express "last month". And from a usability perspective, natural language is just unbeatable.

About the conflict with i18n, this is the exact reason why I only support a minimal subset of possible natural language time specs, and everything is localized in a single function. I'm not adding a new problem: it was there already, with parse_date() already supporting "now" and various date/time formats. i18n will have to solve this problem anyway, probably by having language-specific date parsing functions. In the meantime, the natural language parsing functionality is close to parse_date(), nicely packaged, only waiting to be refactored and possibly moved to Babel.

[rblank]

Replying to rblank:

What about ; or _? They would seem good candidates as separators.

Even better, I should use |, as it's already used as a separator for select fields.

[mikec@…]

Replying to rblank:

The current modifiers are !~^$, so I had to select another "special" character. Other characters to avoid are :-,/.. On my keyboard, this reasonably leaves the following set: +&=?*%[]{}@#<>;_, although I wouldn't recommend the first four. I picked @ at random (and maybe because in french, "at" can be literally translated as "to" (as in "from ... to ...")). What about ; or _? They would seem good candidates as separators.

I'd suggest '>' -- that looks like an arrow and so might indicate "to". Another possibility would be to use an actual ellipsis character "…" -- allow the user to type three dots, but parse it into an ellipsis and use that for the internals string.

Then I thought of using something like "30d" for 30 days, but I couldn't find a good way to express "last month". And from a usability perspective, natural language is just unbeatable.

In Bugzilla, they indicate "last month" with "1m". "10d" is ten days, "2m" is two months, "2y" is two years. I certainly wouldn't complain if the natural-language form was left in place, but the Bugzilla style is natural to me and saves keystrokes.

[osimons]

Replying to rblank:

What about ; or _? They would seem good candidates as separators.

Even better, I should use |, as it's already used as a separator for select fields.

Well put arguments about separator above, and agree that reusing existing modifiers is not really a red-hot idea... My personal favorites are | and ; and #, but I'll leave selection to you :-)

As for natural language, I'm fine with it. Having mostly non-English users, I suppose the short Bugzilla style mentioned will have the greatest chance of seeing any usage.

[rblank]

Updated patch fixing unit tests and a few issues

[rblank]

Here's an updated patch, with the following changes:

• Quite a lot of unit test were failing (notification, CSV generation). Fixed the implementation to make the tests pass again.
• Changed the separator to ;. The pipe | was not such a great idea, as it would have required to special-case query string parsing.
• Added bugzilla-style relative time specifications: h, d, w, m, y.
• Added unit tests for SQL generation of time-based queries.

At this point, all unit tests pass, and I'm quite happy with the implementation. So if this patch can get some more testing (possibly from people subscribed to this ticket), I'll be able to commit it soon.

[rblank]

Updated patch for current trunk

[rblank]

As this is quite a consequent change, I'm still looking for another volunteer to review this. Any takers?

[cboos]

Patch looks very good, but as you said as it's quite a big change, I'd like to test it over the week-end. Thanks for the reminder ;-)

[cboos]

The deeper code changes are fine, however I think the UI could still be improved. I've no objection to committing the fix as is though, the improvements could be done later on.

I still think that what I originally proposed:

Created after ________| and before ________|

and use datepicker to get one date per field.

would be clearer than what we have now:

Created |is between    |  _________| and ________|
        |is not between|

Maybe something like:

Created |during period    | starting from: ________| ending at: __________| (MM/DD/YYYY)
        |outside of period|

I even wonder if it's worth having the during/outside of period choice (i.e. your is/is not between). Most often, you'd want one of:

• starting from X (open ended)
• ending at Y (open ended for the start date)
• starting from X; ending at Y

How likely is it that you'd want to exclude a given period (say 01/01/2005;01/01/2006)? If so, you could do with two open ended constraints:

• starting from 01/01/2006
• ending at 01/01/2005

[rblank]

Replying to cboos:

and use datepicker to get one date per field.

I agree about the datepicker. I had actually started integrating it, but I backed out the change for three reasons:

• There are many other date fields in Trac that could benefit from the datepicker.
• The datepicker might need configuration options or locale-dependent logic (e.g. for setting the first day of the week), which I am not familiar with.
• The current version of the datepicker has a bug when setting the first day of the week, where it scrambles the days completely. Quite annoying.

So my idea was indeed to commit the simple version with the logic and just text fields first, then to work on adding datepickers for all date fields in Trac as a separate task.

I even wonder if it's worth having the during/outside of period choice (i.e. your is/is not between).

Yeah, actually me too. The only reason why I added "is not between" was for consistency with the other field types, which always have a predicate and its negative (is / is not, ...). I'll remove the "inside/outside period" choice.

I still tend to prefer "between ... and ...", though, rather than "after ... and before ..." , without actually being able to articulate why.

[rblank]

Removed mode selection, always use "between ... and ..."

[rblank]

I have removed the "is between/is not between" selector and have replaced it with a static label "between". This was actually not trivial, because all fields allowed more than once in a query have the mode selector, and it is used for locating the first item on deletion. I have changed this to add an id to the field label and locate the label instead.

The changes w.r.t. the previous patch are mostly in query.js and query.html.

I'll do some more testing, and if nothing else pops up, I'll commit the patch as a first step.

[anonymous]

Replying to rblank:

I even wonder if it's worth having the during/outside of period choice (i.e. your is/is not between).

Yeah, actually me too. The only reason why I added "is not between" was for consistency with the other field types, which always have a predicate and its negative (is / is not, ...). I'll remove the "inside/outside period" choice. I still tend to prefer "between ... and ...", though, rather than "after ... and before ..." , without actually being able to articulate why.

This approach, with having between and two values for the same filter, doesn't seem, to me, to be consistent with how custom query works now. The answer, for me, is the implementation of > < comparison operators. This way the user has more flexibility in creating queries. I, as an user and plugin developer, need trac to let me make the decisions. Implementing date support in the custom query using between isn't flexible at all and forces me into a pattern. I know that adding the operators is another feature that people requested and there are open tickets on that but for me the two issues are related.

[rblank]

Replying to anonymous:

Implementing date support in the custom query using between isn't flexible at all and forces me into a pattern.

Could you elaborate on how the implementation using "between" is inflexible? Remember that if you leave the first field empty, you essentially get a < operator, and if the second is empty, it becomes a >.

I would even say that the opposite is true. If you only have < and >, how do you get all tickets between two dates? You would need a query with an AND, something that Trac doesn't support so far (when specifying several constraints on the same field, they are combined with an OR).

[cboos]

I think it's true that with the "between" wording, you somehow feel compelled to enter an interval, at first. That's why I think the after/before wording (or similar) might still be a slightly better choice, as it reads better for open intervals.

Appreciate how the query would "read" in either case:

Created later than |2 months ago| and earlier than |________|

vs.

Created between |2 months ago| and |________|

But this is just a minor wording detail, your code, your take Remy ;-)

[rblank]

Replying to cboos:

Created later than |2 months ago| and earlier than |________|

Heh, what I like best is:

Created between |2 months ago| and |now|

But this is just a minor wording detail, your code, your take Remy ;-)

I'll stick with "between" for now, but if people bug me enough about it after having used the feature for some time, I'll change to whatever is suggested most often.

[rblank]

Patch applied in [7588]. I'll update the documentation for the query syntax shortly.

[rblank]

The documentation in TracQuery#QueryLanguage has been updated.

[cboos]

Great work Remy!

Replying to rblank:

Replying to cboos:

Created later than |2 months ago| and earlier than |________|

Heh, what I like best is: Created between |2 months ago| and |now|

... and of course the converse:

Created between |the dawn of time| and |2 months ago|

:-)

Btw, now that you must be more familiar with query.js, do you feel like rewriting it using jQuery or should I keep that task on my list?

[rblank]

Replying to cboos:

Created between |the dawn of time| and |2 months ago|

:-)

Btw, now that you must be more familiar with query.js, do you feel like rewriting it using jQuery or should I keep that task on my list?

Is there a ticket for that already? I might just grab it :-)

More seriously, is that something we want to do? The current code, while not the most elegant, seems to be working pretty fine, doesn't it?

[cboos]

Replying to rblank:

Btw, now that you must be more familiar with query.js, do you feel like rewriting it using jQuery or should I keep that task on my list?

Is there a ticket for that already? I might just grab it :-)

Closest thing is #7111

More seriously, is that something we want to do? The current code, while not the most elegant, seems to be working pretty fine, doesn't it?

Yes, I think that's something we should do. The code is big and obscure, not the best foundation for future improvements (e.g. #2647, #5526 and many others).

Btw, when looking at the query:?keywords=~query&status=!closed tickets, I saw #1942, following-up there...