TicketSQL Date parse weird

Stuart_Browne · June 5, 2012, 8:06am

Hi,

Using RHEL5, RT 3.8.8, Time::DateParse 2006.0814.

When doing a TicketSQL search similar to the following:

Status = 'open' AND Due <= '10am tomorrow'

The time portion of the string isn’t taken into account.

On the command line, a quick test of Time::DateParse shows it works:

]# perl -e ‘use Time::ParseDate; print parsedate(“2012-06-06 10:00”) . “\n”; print parsedate(“10am tomorrow”) . “\n”;’
1338940800
1338940800

So it appears as if RT is parsing the date string of ‘Due’ and messing with it. Using the time portion before or after, using 10am vs 10:00, none appear to work. Using any time component has the same result (thinking it may be a timezone issue. It wasn’t).

I can’t see in the RT code where this might be happening (been looking in SQL.pm). Can someone point me in the right direction?

Thanks

Stuart J. Browne
Senior Unix Administrator, Network Administrator, Database Administrator
AusRegistry Pty Ltd
Level 8, 10 Queens Road
Melbourne. Victoria. Australia. 3004.
Ph: +61 3 9866 3710
Fax: +61 3 9866 1970
Email: stuart.browne@ausregistry.com.au
Web: www.ausregistry.com.au

The information contained in this communication is intended for the named recipients only. It is subject to copyright and may contain privileged and/or confidential information. If you are not an intended recipient you must not use, copy, distribute or take any action in reliance on it. If you have received this communication in error, please delete all copies from your system and notify us immediately.

Kevin_Falcone · June 7, 2012, 3:00am

When doing a TicketSQL search similar to the following:

Status = ‘open’ AND Due <= ‘10am tomorrow’

The time portion of the string isn’t taken into account.

On the command line, a quick test of Time::DateParse shows it works:

]# perl -e ‘use Time::ParseDate; print parsedate(“2012-06-06 10:00”) . “\n”; print parsedate(“10am tomorrow”) . “\n”;’
1338940800
1338940800

So it appears as if RT is parsing the date string of ‘Due’ and messing with it. Using the time portion before or after, using 10am vs 10:00, none appear to work. Using any time component has the same result (thinking it may be a timezone issue. It wasn’t).

I can’t see in the RT code where this might be happening (been looking in SQL.pm). Can someone point me in the right direction?

RT actually passes a number of arguments to parsedate:

grep can find where it’s called (not in SQL.pm)

$ grep -r Time::ParseDate::parsedate lib/
lib/RT/Articles.pm: my $seconds = Time::ParseDate::parsedate( $args{$date}, FUZZY => 1, PREFER_PAST => 1 );
lib/RT/Date.pm: my $date = Time::ParseDate::parsedate(

-kevin

Stuart_Browne · June 7, 2012, 7:51am

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-
bounces@lists.bestpractical.com] On Behalf Of Kevin Falcone
Sent: Thursday, 7 June 2012 1:00 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] TicketSQL Date parse weird…

When doing a TicketSQL search similar to the following:

RT actually passes a number of arguments to parsedate:

grep can find where it’s called (not in SQL.pm)

$ grep -r Time::ParseDate::parsedate lib/
lib/RT/Articles.pm: my $seconds = Time::ParseDate::parsedate(
$args{$date}, FUZZY => 1, PREFER_PAST => 1 );
lib/RT/Date.pm: my $date = Time::ParseDate::parsedate(

-kevin

Ok, further test (see attached perl routine):

[root@psg-apps tmp]# ./test.pl.txt
current time() output: 1339054410

fixed-date Time::ParseDate: 1339113600
tomorrow 10am Time::ParseDate: 1339113600

fixed-date RT::Date output: 1339149600
tomorrow 10am RT::Date output: 1339200000
timezone according to RT: 36000

Using either ‘user’ or ‘server’ as the timezone I get the same result but I’m probably missing something here for the fixed-date (I can accept that the date might be being modified elsewhere I’m yet to find), but the ‘tomrorow 10am’ shouldn’t be a full day off. So the adding of the timezone offset before Time::ParsEdate::parsedate then removing it again appears to be causing the issue here; pushing today past the end of the day boundary.

I’ll try this routine tomorrow morning (before 10am) to see if it shows the right thing, then try to figure out a way to get it to return the same result for the two RT::Date outputs.

Stuart

test.pl.txt (803 Bytes)

Ruslan_Zakirov1 · June 7, 2012, 10:25am

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-
bounces@lists.bestpractical.com] On Behalf Of Kevin Falcone
Sent: Thursday, 7 June 2012 1:00 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] TicketSQL Date parse weird…

When doing a TicketSQL search similar to the following:

RT actually passes a number of arguments to parsedate:

grep can find where it’s called (not in SQL.pm)

$ grep -r Time::ParseDate::parsedate lib/
lib/RT/Articles.pm: my $seconds = Time::ParseDate::parsedate(
$args{$date}, FUZZY => 1, PREFER_PAST => 1 );
lib/RT/Date.pm: my $date = Time::ParseDate::parsedate(

-kevin

Ok, further test (see attached perl routine):

[root@psg-apps tmp]# ./test.pl.txt
current time() output: 1339054410

fixed-date Time::ParseDate: 1339113600
tomorrow 10am Time::ParseDate: 1339113600

fixed-date RT::Date output: 1339149600
tomorrow 10am RT::Date output: 1339200000
timezone according to RT: 36000

Using either ‘user’ or ‘server’ as the timezone I get the same result but I’m probably missing something here for the fixed-date (I can accept that the date might be being modified elsewhere I’m yet to find), but the ‘tomrorow 10am’ shouldn’t be a full day off. So the adding of the timezone offset before Time::ParsEdate::parsedate then removing it again appears to be causing the issue here; pushing today past the end of the day boundary.

I can believe it’s wrong. Time::ParseDate can do timezones, but
timezones support is very limited, so we do our timezones after
parsing in UTC. Understandably ‘tomorrow 10am’ in london while you’re
somewhere else is completely different thing.

We do some compensation in NOW argument and in theory it should work.
Are you on the latest version of the module?

Never noticed before that ZONE argument can take whatever TZ
environment variable can take. May be attached patch will just work.
Have not tested it. Applies cleanly to 3.8 and 4.0.

I’ll try this routine tomorrow morning (before 10am) to see if it shows the right thing, then try to figure out a way to get it to return the same result for the two RT::Date outputs.

Stuart

Best regards, Ruslan.

RT-4.x-use-parsedate-timezones-support.patch (1.16 KB)

Stuart_Browne · June 8, 2012, 7:12am

Using either ‘user’ or ‘server’ as the timezone I get the same result
but I’m probably missing something here for the fixed-date (I can accept
that the date might be being modified elsewhere I’m yet to find), but the
‘tomrorow 10am’ shouldn’t be a full day off. So the adding of the
timezone offset before Time::ParsEdate::parsedate then removing it again
appears to be causing the issue here; pushing today past the end of the
day boundary.

I can believe it’s wrong. Time::ParseDate can do timezones, but
timezones support is very limited, so we do our timezones after
parsing in UTC. Understandably ‘tomorrow 10am’ in london while you’re
somewhere else is completely different thing.

We do some compensation in NOW argument and in theory it should work.
Are you on the latest version of the module?

Never noticed before that ZONE argument can take whatever TZ
environment variable can take. May be attached patch will just work.
Have not tested it. Applies cleanly to 3.8 and 4.0.

I’ll try this routine tomorrow morning (before 10am) to see if it shows
the right thing, then try to figure out a way to get it to return the same
result for the two RT::Date outputs.

Ruslan,

I’m using RT 3.8.8 at the moment. did some tests this morning (before the 10-hour’s from UTC) and all the times aligned. With your patch, the worded time was off by TZ:

BEFORE 10AM

Before patch

[bekar@psg-apps tmp]$ ./test.pl
current time() output: 1339111474