[rt-users] Dynamic/Relative Date Searching

[Stephen Dowdy]

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 at ucar.edu  -  http://www.rap.ucar.edu/~sdowdy/