[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 opens                office closes
> 
> Monday-Thursday       8:00:00                        16:00:00
> Friday                          8:00:00                        15: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!
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 195 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20100903/935f8f70/attachment.sig>