[rt-users] Problem in searching date

[Kevin Falcone]

On Wed, Sep 03, 2014 at 04:40:04PM +0800, Chrilly Cheng wrote:
> Hi Alex,
>  
> Thanks a lot for your explanation. I've done that config  to set 
> $AmbiguousDayInPast to 1, but the query still not work. I changed it a little
> bit:
> 
> Queue = 'ChangeRequest' AND (  Status = 'InProgress' Or Status =
> 'WaitingforApproval' ) AND 'CF.{Proposed Start Time(GMT)}' > 'midnight Sunday'
> AND 'CF.{Proposed Start Time(GMT)}' < 'midnight next Sunday' 
> 
> This query needs to be executed every Wednesday to send out dashboard report.
> It will search for tickets whose "Proposed Start Time" is between last Sunday
> and this Sunday.
> 
>  And unfortunately, I cant find any log about this query. I'm using it on the
> New Search page, does there have any other way to test query?
> 

A few things

Your use of GMT in the CF name is... misleading, since Fields are set
in the User's Time Zone, this may not work out as you intend.
Additionally, relative dates are going to be calculated in the user's
time zone.  This means "midnight next sunday" will *actually* be
2014-09-07 04:00:00 when it goes and looks in the DB (since I'm four
hours behind GMT).  Keep in mind also, did you mean Midnight at the
start of sunday or midnight at the end of sunday?

You can get exact parsing of the query by running code on the command
line.  Save this in a file and run it.

use lib '/opt/rt4/lib/';
use RT -init;

my $t = RT::Tickets->new(RT->SystemUser);
$t->FromSQL("Your Query");
warn $t->BuildSelectQuery;

-kevin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 221 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20140903/459cc1e2/attachment.sig>