[rt-users] Problems with SQL-time functions
Wolfram Huettermann
wolfram.huettermann at desy.de
Fri Sep 3 05:30:17 EDT 2010
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.
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
More information about the rt-users
mailing list