[Rt-devel] Custom fields as dates patch

[Joop]

Joop wrote:
> Found one more problem.
> I have entered a date through the Calendar popup (2010-07-23) and 
> saved it.
> Then I tried to search for it and wasn't able to find the ticket back. 
> Looking at the database I discovered why.
> It is stored in the database (ObjectCustomFieldValues) as 2010-07-22 
> 22:00:00
> The datepicker picks the date in in my Timezone (GMT+1, +1 for DST 
> =+2) but its stored in the database as GMT. That is not the problem in 
> itself but the date entered in the Query builder is NOT converted to 
> GMT and so never finds the ticket.
Using cf-date branch is I what should have done instead of manually 
patching HEAD with a single commit from cf-date. Using branch cf-date 
did result in a correct working search with a date CF but only when 
looking for ON not with BEFORE or AFTER

The reason that fails is because of the queries being build. The ON 
query looks like:
SELECT COUNT (DISTINCT main.id)
  FROM    Tickets main
       JOIN
          ObjectCustomFieldValues ObjectCustomFieldValues_1
       ON     (ObjectCustomFieldValues_1.CustomField = '327')
          AND (ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket')
          AND (ObjectCustomFieldValues_1.Disabled = '0')
          AND (ObjectCustomFieldValues_1.ObjectId = main.id)
 WHERE (main.Status != 'deleted')
       AND (main.Queue = '13'
            AND ( ( ( (ObjectCustomFieldValues_1.Content >=
                          '2010-07-25 22:00:00'
                       AND ObjectCustomFieldValues_1.Content <=
                              '2010-07-26 22:00:00')))))
       AND (main.TYPE = 'ticket')
       AND (main.EffectiveId = main.id)

And the BEFORE/AFTER looks like:
SELECT main.*
  FROM    Tickets main
       JOIN
          ObjectCustomFieldValues ObjectCustomFieldValues_1
       ON     (ObjectCustomFieldValues_1.CustomField = '327')
          AND (ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket')
          AND (ObjectCustomFieldValues_1.Disabled = '0')
          AND (ObjectCustomFieldValues_1.ObjectId = main.id)
 WHERE (main.Status != 'deleted')
       AND (main.Queue = '13'
            AND ( ( (ObjectCustomFieldValues_1.Content < '2010-07-29'
                     OR ( (ObjectCustomFieldValues_1.Content = ''
                           OR ObjectCustomFieldValues_1.Content IS NULL)
--                         AND ObjectCustomFieldValues_1.LargeContent <
--                                '2010-07-29'
)))))
       AND (main.TYPE = 'ticket')
       AND (main.EffectiveId = main.id)

This query works and returns the expected result because of the two 
commented lines. If I run the original query it doesn't since Oracle 
doesn't like comparing CLOB columns like LargeContent against a constant.

I don't know why LargeContent is used only in BEFORE/AFTER queries and 
not with ON. Solutions seems to be to not use LargeContent but don't 
know the ramifications of that.

Regards,

Joop