Dynamic/Relative Date Searching

We have the need to create a search that uses relative dates. Say I want a
search that will show me all work completed in the last month. If I select
’Resolved’->‘After’ I then have to manually enter a date from a month ago in
order to get that result. Then, if I want to bookmark that search result,
it becomes outdated almost immediately. Basically what we’re looking for is
something that would allow us to create searches like:

Created - After Last Month
Resolved - After Last Week
Due - Before Next Week
Starts - Before Next Month

Then they could be bookmarked and always accurate/up-to-date.

Has there been a patch or has anyone added some options to the ‘Before’ ‘On’
‘After’ that may be more relative in nature? (or maybe a 3rd drop down?)

Thanks,

Ryan Roland

Application Developer
Information Technology
Division of Recreational Sports
Indiana University

smime.p7s (2.96 KB)

We have the need to create a search that uses relative dates. Say I
want a search that will show me all work completed in the last
month. If I select ‘Resolved’->‘After’ I then have to manually enter
a date from a month ago in order to get that result.

Created After “1 month ago”

The routines for the user interface use Time::ParseDate to figure out what
you are using for a date. Take a look at the man page for a long list of
absolute and relative formats. It’s pretty clever.

Russell Mosemann, Ph.D. * Computing Services * Concordia University, Nebraska
"There are only two things which are sure in this life, death and taxes.
However, death doesn’t get worse every time Congress meets."

Roland, Ryan M wrote:

it becomes outdated almost immediately. Basically what we’re looking for is
something that would allow us to create searches like:

Created - After Last Month
Resolved - After Last Week
Due - Before Next Week
Starts - Before Next Month

Roland,

You can do things like:

Query:   Created > '31 days ago'

To get all tickets whose creation time is larger than 31 days ago (i know, that statement doesn’t look right, since it reads (to me at least) as Created more than 31 days ago, but it’s a scalar comparison with a monotonically increasing number)

I’ve started playing with Group SavedSearches, which if combined with Stephen Turner’s SavedSearches MASON element (http://wiki.bestpractical.com/index.cgi?HomePageSavedSearches) provides a real handy way to create shared saved queries that everyone in your group can use from the “At a Glance” home page.

I’ve tried creating savedsearches such as:

Name: "Today’s Tickets"
Function: Display all the tickets (of any state) that came in today

So far, the best thing i can find for this one is:

Created > 'Yesterday'

But that seems equivalent to

Created > '24 hours ago'

As i get stuff upto 24 hours old, even if it’s 8am.

I initially tried

Created > 'Today'

But that stopped showing me tickets from the morning by the time evening rolled around. (I’d figured “Today” is equivalent to a full time spec of TODAY @ 00:00 a.m.). I can’t seem to figure out how to specify an absolute HOUR clock time like “Created > ‘08:00’” That doesn’t seem to work.

I’m not sure if these date references are standard SQL time comparisons or specific to RT yet. I’m gonna peruse the RT source and the MySQL documentation to see.
(a cursory look doesn’t show the string “YESTERDAY” in the RT sources and i can’t find it documented in the MySQL docs either, hmmm)

–stephen
Stephen Dowdy - Systems Administrator - NCAR/RAP
303.497.2869 - sdowdy@ucar.edu - http://www.rap.ucar.edu/~sdowdy/

Well, taking Ken and Russell’s lead, I did a little more research and,
finding how useful this was, figured that it would be handy to have this in
the Searching section of the wiki:

http://wiki.bestpractical.com/index.cgi?ManualUsingWebInterface

Scan that and if anyone has anything that should be added or feels should be
removed, please do so.

Thanks,

Ryan Roland

Application Developer
Information Technology
Division of Recreational Sports
Indiana UniversityFrom: Kenneth Marshall [mailto:ktm@it.is.rice.edu]
Sent: Friday, February 25, 2005 10:26
To: Roland, Ryan M
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Dynamic/Relative Date Searching

We have the need to create a search that uses relative dates. Say I want
a
search that will show me all work completed in the last month. If I
select
’Resolved’->‘After’ I then have to manually enter a date from a month ago
in
order to get that result. Then, if I want to bookmark that search result,
it becomes outdated almost immediately. Basically what we’re looking for
is
something that would allow us to create searches like:

Created - After Last Month

Created - After - “-1 month”

Resolved - After Last Week

Resolve - After - “-1 week”

Due - Before Next Week

Due - Before - “+1 week”

Starts - Before Next Month

Starts - Before - “+1 month”

Ken

smime.p7s (2.96 KB)

We have the need to create a search that uses relative dates. Say I want a
search that will show me all work completed in the last month. If I select
’Resolved’->‘After’ I then have to manually enter a date from a month ago in
order to get that result. Then, if I want to bookmark that search result,
it becomes outdated almost immediately. Basically what we’re looking for is
something that would allow us to create searches like:

Created - After Last Month

Created - After - “-1 month”

Resolved - After Last Week

Resolve - After - “-1 week”

Due - Before Next Week

Due - Before - “+1 week”

Starts - Before Next Month

Starts - Before - “+1 month”

Ken