[rt-users] Problems with SQL-time functions

[Wolfram Huettermann]

Hello,

I was given the task to filter all the tickets inside or outside fixed 
business hours in a specified date range. I will display you table to 
demonstrate what I mean:


weekday  office opensoffice closes

Monday-Thursday   8:00:0016:00:00
Friday  8:00:0015:30:00

In usual SQL, one would filter weekday by weekday and exclude the time 
outside or inside working hours. If you only want to enlist the Tickets 
inside business hours, you can use the TIME and WEEKDAY functions, the 
SQL statement would look like that:


Select id, Created, Subject from Tickets where WEEKDAY(Created) not in 
(0,6) and ((WEEKDAY(Created) in(1, 2, 3, 4) and TIME(Created)  
'08:00:00' and TIME(Created)  '16:00:00') or (WEEKDAY(Created)=5 and 
TIME(Created)  '08:00:00' and TIME(Created)  '15:30:00'))



If you put the statement after the where-clause into the method FormSQL 
of the class RT::Ticket, you will not get the right results, but /all/ 
tickets.



I do not know why that method does not work. Does anybody have any 
hints? Or is it just impossible to do so?


Greetings,

Wolfram




RT Training in Washington DC, USA on Oct 25  26 2010
Last one this year -- Learn how to get the most out of RT!

Re: [rt-users] Problems with SQL-time functions

[Kevin Falcone]

On Fri, Sep 03, 2010 at 11:30:17AM +0200, Wolfram Huettermann wrote:
 Hello,
 
 I was given the task to filter all the tickets inside or outside
 fixed business hours in a specified date range. I will display you
 table to demonstrate what I mean:
 
 weekday  office opensoffice closes
 
 Monday-Thursday   8:00:0016:00:00
 Friday  8:00:0015:30:00
 
 In usual SQL, one would filter weekday by weekday and exclude the
 time outside or inside working hours. If you only want to enlist the
 Tickets inside business hours, you can use the TIME and WEEKDAY
 functions, the SQL statement would look like that:
 
 Select id, Created, Subject from Tickets where WEEKDAY(Created) not
 in (0,6) and ((WEEKDAY(Created) in(1, 2, 3, 4) and TIME(Created) 
 '08:00:00' and TIME(Created)  '16:00:00') or (WEEKDAY(Created)=5
 and TIME(Created)  '08:00:00' and TIME(Created)  '15:30:00'))
 
 
 If you put the statement after the where-clause into the method
 FormSQL of the class RT::Ticket, you will not get the right results,
 but /all/ tickets.

FromSQL isn't SQL, it is TicketSQL.  I bet it is throwing away a
number of your constructs that it doesn't understand.  You may be able
to construct what you need using a vanilla Limit and the FUNCTION
arguments.

-kevin

 I do not know why that method does not work. Does anybody have any
 hints? Or is it just impossible to do so?
 
 Greetings,
 
 Wolfram
 
 
 
 
 RT Training in Washington DC, USA on Oct 25  26 2010
 Last one this year -- Learn how to get the most out of RT!


pgpUGtM3AKe2F.pgp
Description: PGP signature

RT Training in Washington DC, USA on Oct 25  26 2010
Last one this year -- Learn how to get the most out of RT!