Search for tickets in queue with cf storing date, based on date

I am working on a notification that is supposed to be generated for
tickets based on a date entered into a custom field. As such I need to
search for tickets where the date in the field is in a specific range. I
cannot use the date fields already in RT tickets as we have other plans
for those.

I created this SQL as an example of what I am working for. It is a
postgres database so table/column capitalization is unimportant.

SELECT t.id FROM
objectcustomfieldvalues AS v
JOIN tickets AS t ON (v.objectid = t.id)
JOIN queues AS q ON (t.queue = q.id)
JOIN customfields AS c ON ( c.id = v.customfield )
WHERE
objecttype = 'RT::Ticket’
AND q.name = 'SP-Delivery’
AND date(v.content) > (now() - interval ‘1 day’)
AND c.name = ‘Expected Decision Date’;

I was looking at http://wiki.bestpractical.com/view/SearchOnDates which
does not seem to pertain in this case. As well I have been studying
SBIx::Searchbuilder and the SQL libraries in RT. It would appear
DBIx::SearchBuilder and the other code does not have any system for
doing a limit on a value cast as a specific type.

I was going to go ahead and write a plugin that connects to the DB
manually, finds the tickets, and returns a Tickets object with them. But
I wanted to check in here first to see if there was already a solution,
or something simple I could use.

Thank you,

Chad

signature.asc (252 Bytes)

I am working on a notification that is supposed to be generated for
tickets based on a date entered into a custom field. As such I need to
search for tickets where the date in the field is in a specific range. I
cannot use the date fields already in RT tickets as we have other plans
for those.

Maybe you can try the following patch with which you should be able to
do date comparaison like other dates in RT:

http://rt3.fsck.com/Ticket/Display.html?id=8721&user=guest&pass=guest