Problem in searching date

Do I need to do any setting of the week on server?On Tue, Sep 2, 2014 at 3:59 PM, Chrilly Cheng chrilde@gmail.com wrote:

Hi Guys,

I was trying to create a qeury which returns all open tickets whose
“Proposed Start Date” are in this week, from specific queue. I tried with
some build-in variables in searching as showing below:

Queue = ‘ChangeRequest’ AND ( Status != ‘rejected’ AND Status !=
‘resolved’ ) AND ‘CF.{Proposed Start Time}’ > ‘last Sunday’ AND
‘CF.{Proposed Start Time}’ < ‘Sunday’

but this query returns nothing. BTW, “Proposed Start Time” is a datetime
custom field applied to this queue.

Can you please help to check on this, see whether it doesn’t work? Many
thanks.

Br
Vodar

What does the debug log say when you run the search?

There are some config options relevant to relative dates, but it’s worth
verifying first that the two dates are being parsed at all.

P.S. I believe that the built-in Starts ticket attribute is intended to
function as a proposed start time.On 02/09/2014 8:25 pm, “Chrilly Cheng” chrilde@gmail.com wrote:

Do I need to do any setting of the week on server?

On Tue, Sep 2, 2014 at 3:59 PM, Chrilly Cheng chrilde@gmail.com wrote:

Hi Guys,

I was trying to create a qeury which returns all open tickets whose
“Proposed Start Date” are in this week, from specific queue. I tried with
some build-in variables in searching as showing below:

Queue = ‘ChangeRequest’ AND ( Status != ‘rejected’ AND Status !=
‘resolved’ ) AND ‘CF.{Proposed Start Time}’ > ‘last Sunday’ AND
‘CF.{Proposed Start Time}’ < ‘Sunday’

but this query returns nothing. BTW, “Proposed Start Time” is a datetime
custom field applied to this queue.

Can you please help to check on this, see whether it doesn’t work? Many
thanks.

Br
Vodar


RT Training - Boston, September 9-10
http://bestpractical.com/training

Hi Alex,

Thanks for replying.

I found logs below, but no more details about it.

[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘Sunday’ 0 (/usr/share/request-tracker4/lib/RT/Date.pm:222)
[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘last Sunday’ 1409482532 (/usr/share/request-tracker4/lib/RT/Date.pm:222)
[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘Sunday’ 0 (/usr/share/request-tracker4/lib/RT/Date.pm:222)
[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘last Sunday’ 1409482532 (/usr/share/request-tracker4/lib/RT/Date.pm:222)

And thanks for your reminding, I’m also aware of this, but our manager
prefer that label, which is hard to config with the built-in attribute in
RT.

Br
VodarOn Tue, Sep 2, 2014 at 6:35 PM, Alex Peters alex@peters.net wrote:

What does the debug log say when you run the search?

There are some config options relevant to relative dates, but it’s worth
verifying first that the two dates are being parsed at all.

P.S. I believe that the built-in Starts ticket attribute is intended to
function as a proposed start time.
On 02/09/2014 8:25 pm, “Chrilly Cheng” chrilde@gmail.com wrote:

Do I need to do any setting of the week on server?

On Tue, Sep 2, 2014 at 3:59 PM, Chrilly Cheng chrilde@gmail.com wrote:

Hi Guys,

I was trying to create a qeury which returns all open tickets whose
“Proposed Start Date” are in this week, from specific queue. I tried with
some build-in variables in searching as showing below:

Queue = ‘ChangeRequest’ AND ( Status != ‘rejected’ AND Status !=
‘resolved’ ) AND ‘CF.{Proposed Start Time}’ > ‘last Sunday’ AND
‘CF.{Proposed Start Time}’ < ‘Sunday’

but this query returns nothing. BTW, “Proposed Start Time” is a
datetime custom field applied to this queue.

Can you please help to check on this, see whether it doesn’t work? Many
thanks.

Br
Vodar


RT Training - Boston, September 9-10
http://bestpractical.com/training

Your first debug line indicates that “Sunday” alone is not being parsed
into a meaningful time value. This can be corrected by setting RT config
option $AmbiguousDayInPast to 1.

Your second debug line indicates that “last Sunday” is resolving to the
immediately previous Sunday, not the Sunday before the immediately previous
Sunday (which I suspect you want). The Examples section of the
documentation for the most recent version (2013.1113 right now) of
Time::ParseDate[1] lists “Sunday before last” as valid input. This might
come closer to achieving your desired outcome.

These times will not resolve to a date boundary, like midnight—they will
have the correct day, but use the current time. This is probably not what
you want. Consider prefixing “midnight” to each of your search values,
i.e. “midnight Sunday” and “midnight Sunday before last”.

If you can’t get the search to work as desired, you could simplify the
search to return matches between the current time and “1 week ago” and run
it weekly at midnight on Sunday via a dashboard email subscription.

[1] Time::ParseDate - date parsing both relative and absolute - metacpan.org 2 September 2014 21:27, Chrilly Cheng chrilde@gmail.com wrote:

Hi Alex,

Thanks for replying.

I found logs below, but no more details about it.

[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘Sunday’ 0 (/usr/share/request-tracker4/lib/RT/Date.pm:222)
[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘last Sunday’ 1409482532 (/usr/share/request-tracker4/lib/RT/Date.pm:222)
[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘Sunday’ 0 (/usr/share/request-tracker4/lib/RT/Date.pm:222)
[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘last Sunday’ 1409482532 (/usr/share/request-tracker4/lib/RT/Date.pm:222)

And thanks for your reminding, I’m also aware of this, but our manager
prefer that label, which is hard to config with the built-in attribute in
RT.

Br
Vodar

On Tue, Sep 2, 2014 at 6:35 PM, Alex Peters alex@peters.net wrote:

What does the debug log say when you run the search?

There are some config options relevant to relative dates, but it’s worth
verifying first that the two dates are being parsed at all.

P.S. I believe that the built-in Starts ticket attribute is intended to
function as a proposed start time.
On 02/09/2014 8:25 pm, “Chrilly Cheng” chrilde@gmail.com wrote:

Do I need to do any setting of the week on server?

On Tue, Sep 2, 2014 at 3:59 PM, Chrilly Cheng chrilde@gmail.com wrote:

Hi Guys,

I was trying to create a qeury which returns all open tickets whose
“Proposed Start Date” are in this week, from specific queue. I tried with
some build-in variables in searching as showing below:

Queue = ‘ChangeRequest’ AND ( Status != ‘rejected’ AND Status !=
‘resolved’ ) AND ‘CF.{Proposed Start Time}’ > ‘last Sunday’ AND
‘CF.{Proposed Start Time}’ < ‘Sunday’

but this query returns nothing. BTW, “Proposed Start Time” is a
datetime custom field applied to this queue.

Can you please help to check on this, see whether it doesn’t work? Many
thanks.

Br
Vodar


RT Training - Boston, September 9-10
http://bestpractical.com/training

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?

Many Thanks!

Br
VodarOn Tue, Sep 2, 2014 at 7:43 PM, Alex Peters alex@peters.net wrote:

Your first debug line indicates that “Sunday” alone is not being parsed
into a meaningful time value. This can be corrected by setting RT config
option $AmbiguousDayInPast to 1.

Your second debug line indicates that “last Sunday” is resolving to the
immediately previous Sunday, not the Sunday before the immediately previous
Sunday (which I suspect you want). The Examples section of the
documentation for the most recent version (2013.1113 right now) of
Time::ParseDate[1] lists “Sunday before last” as valid input. This might
come closer to achieving your desired outcome.

These times will not resolve to a date boundary, like midnight—they will
have the correct day, but use the current time. This is probably not what
you want. Consider prefixing “midnight” to each of your search values,
i.e. “midnight Sunday” and “midnight Sunday before last”.

If you can’t get the search to work as desired, you could simplify the
search to return matches between the current time and “1 week ago” and run
it weekly at midnight on Sunday via a dashboard email subscription.

[1] Time::ParseDate - date parsing both relative and absolute - metacpan.org

On 2 September 2014 21:27, Chrilly Cheng chrilde@gmail.com wrote:

Hi Alex,

Thanks for replying.

I found logs below, but no more details about it.

[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘Sunday’ 0 (/usr/share/request-tracker4/lib/RT/Date.pm:222)
[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘last Sunday’ 1409482532 (/usr/share/request-tracker4/lib/RT/Date.pm:222)
[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘Sunday’ 0 (/usr/share/request-tracker4/lib/RT/Date.pm:222)
[Tue Sep 2 10:55:32 2014] [debug]: RT::Date used Time::ParseDate to make
‘last Sunday’ 1409482532 (/usr/share/request-tracker4/lib/RT/Date.pm:222)

And thanks for your reminding, I’m also aware of this, but our manager
prefer that label, which is hard to config with the built-in attribute in
RT.

Br
Vodar

On Tue, Sep 2, 2014 at 6:35 PM, Alex Peters alex@peters.net wrote:

What does the debug log say when you run the search?

There are some config options relevant to relative dates, but it’s worth
verifying first that the two dates are being parsed at all.

P.S. I believe that the built-in Starts ticket attribute is intended to
function as a proposed start time.
On 02/09/2014 8:25 pm, “Chrilly Cheng” chrilde@gmail.com wrote:

Do I need to do any setting of the week on server?

On Tue, Sep 2, 2014 at 3:59 PM, Chrilly Cheng chrilde@gmail.com wrote:

Hi Guys,

I was trying to create a qeury which returns all open tickets whose
“Proposed Start Date” are in this week, from specific queue. I tried with
some build-in variables in searching as showing below:

Queue = ‘ChangeRequest’ AND ( Status != ‘rejected’ AND Status !=
‘resolved’ ) AND ‘CF.{Proposed Start Time}’ > ‘last Sunday’ AND
‘CF.{Proposed Start Time}’ < ‘Sunday’

but this query returns nothing. BTW, “Proposed Start Time” is a
datetime custom field applied to this queue.

Can you please help to check on this, see whether it doesn’t work?
Many thanks.

Br
Vodar


RT Training - Boston, September 9-10
http://bestpractical.com/training

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

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
VodarOn Thu, Sep 4, 2014 at 4:53 AM, Kevin Falcone falcone@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

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?

If what you want to do didn’t work, I wouldn’t have told you to keep
trying it. What you tried to do works perfectly fine for me locally
on 4.2-trunk (what was released last night as 4.2.7rc1). With all the
caveats I pointed out about time zones and parsing of sunday. See my
example.

You’ve never stated your RT version that I can see, so I assume you’re
using a version that didn’t support this.

If you have an example of:
exact custom field configuration
data in CFs
query written and executed using the script I sent so that we see
the SQL produced, then we can look deeper.

-kevin