[rt-users] Problem in searching date

[Chrilly Cheng]

Hi Alex,

I found the cause of this issue. It's not a problem with "midnight Sunday"
or something, it is about the Custom Field. I set the build-in Starts date
to "Proposed Start Time(GMT)", then run "Queue = 'ChangeRequest' AND Status
!= 'Closed'  AND Starts > 'midnight Sunday before last' ", it returns the
expected results. But when I change Starts to "'CF.{Proposed Start
Time(GMT)}'", it returns nothing.

Does 'CF.{Proposed Start Time(GMT)}' return a datetime value? Or, how can I
set Starts and Due date in scrip?

Please help on these questions, thank a lot!


Br
Vodar


On Thu, Sep 4, 2014 at 4:53 AM, Kevin Falcone <falcone at bestpractical.com>
wrote:

> 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
>
> --
> RT Training - Boston, September 9-10
> http://bestpractical.com/training
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20140905/b1455ad8/attachment.htm>