https://rt-wiki.bestpractical.com/api.php?action=feedcontributions&user=124.148.207.98&feedformat=atomRequest Tracker Wiki - User contributions [en]2024-03-29T05:13:04ZUser contributionsMediaWiki 1.37.2https://rt-wiki.bestpractical.com/index.php?title=TicketSQL&diff=3846TicketSQL2013-07-05T09:18:47Z<p>124.148.207.98: Document relative dates in RT-QL</p>
<hr />
<div>[[TicketSQL]] is RT's loose variant of SQL that you can use for composing custom queries by hand:<br />
<br />
* in RT's web interface on the Query Builder page (click Advanced)<br />
* on the command-line: [[UseRtCrontool|$RTHOME/bin/rt-crontool]] or [[UseRtTool|$RTHOME/bin/rt]], the general-purpose command-line tool<br />
* in RT's Perl API<br />
<br />
For the time being, the best way to learn [[TicketSQL]] is to compose queries using Query Builder and then click Advanced to see the generated [[TicketSQL]] code.<br />
<br />
== Special placeholders ==<br />
<br />
If you would like to search relative to the current user, you can click Advanced and enter something like<br />
<br />
(Owner = '__CurrentUser__')<br />
<br />
You can use __CurrentUser__ anywhere you'd use a user id. This is really useful for building saved searches for people.<br />
<br />
e.g. Owner = '__CurrentUser__' AND Status != 'resolved'<br />
<br />
Bookmarked tickets can be searched too:<br />
<br />
id = '__Bookmarked__'<br />
<br />
== Date Syntax ==<br />
<br />
Date statements take the following form <code><Field> <Operator> <Date></code>. Field is some Ticket Field of type date. Operator is the comparison Operator. Date is a date value.<br />
<br />
Valid operators include:<br />
<br />
* <tt>&lt;</tt> (less than)<br />
* <tt>&lt;=</tt> (less than or equal to)<br />
*<br />
<tt><br />
=</tt> (equals)<br />
* <tt>!=</tt> (not equal to)<br />
* <tt>&gt;</tt> (greater than)<br />
* <tt>&gt;=</tt> (greater than or equal to)<br />
<br />
There are at least three valid date formats:<br />
<br />
* <tt>today</tt> uses today's date as the date value<br />
* <tt>x days ago</tt> where x is some integer value (e.g. 8 days ago)<br />
* <tt>YYYY-MM-DD</tt> absolute day in the format year-month-date (e.g. {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}})<br />
* <tt>'n seconds'</tt>, <tt>'n minutes'</tt>, <tt>'n hours'</tt>, <tt>'n days'</tt>, <tt>'n weeks'</tt> are dates in the future relative to the current date, ie current_date + n minutes. May be negative, eg '-2 days' is two days in the past.<br />
<br />
== Examples ==<br />
<br />
Tickets in the General queue that are new or open and owned by joe:<br />
<br />
(Status = 'new' OR Status = 'open') AND Queue = 'General' AND owner = 'joe'<br />
<br />
Searching by date:<br />
<br />
Created &gt; '7 days ago' AND Queue = 'General'<br />
<br />
Status = 'stalled' AND Due &lt;= 'today'<br />
<br />
Accessing custom fields:<br />
<br />
Status = 'resolved' AND CF.YourCustomField = 'somevalue'<br />
<br />
Tickets that have no members (children):<br />
<br />
HasMember = 'NULL'<br />
<br />
Tickets that depend on at least one other ticket:<br />
<br />
DependsOn != 'NULL'<br />
<br />
== Perl API ==<br />
<br />
my $tickets = RT::Tickets->new(RT->SystemUser);<br />
'''$tickets->FromSQL($tsql);'''<br />
while (my $t = $tickets->Next) {<br />
# do stuff with each ticket $t here<br />
print $t->Subject, "\n";<br />
}</div>124.148.207.98