[rt-users] What’s the correct SQL syntax for a date/time custom field that’s not set?

[Landon Stewart]

Hello,

Basically I want to find tickets with rt-crontool that match the following:
Queue = 'Blocks’ AND Status != ‘removed’ AND CF.{ServerClosed} < ’now'

The problemI’m encountering, although minor, is that it lists tickets that
match that as well as tickets that have no date set whatsoever.  The
rt-crontool utility displays the date as '1970-01-01 00:00:00’ when I
include CF.{ServerClosed} in the -f part of rt-crontool so I thought I’d
use that as part of the query.  I’ve tried variations on “IS NOT NULL” as
!= ‘’ and != ‘0’ as well.  They act the same as “IS NOT NULL”.

*This does not return the expected results which is very puzzling:*
# /opt/rt4/bin/rt ls "Queue = 'Blocks' AND Status != 'removed' AND
CF.{ServerClosed} < 'now' AND CF.{ServerClosed} IS NOT NULL" -f
'id,CF.{ServerClosed}'
*id CF.{ServerClosed}*
xx454020 1970-01-01 00:00:00
xx478114 1970-01-01 00:00:00
xx489293 1970-01-01 00:00:00
xx489299 1970-01-01 00:00:00
xx492598 1970-01-01 00:00:00
xx495471 1970-01-01 00:00:00
xx495481 1970-01-01 00:00:00
xx506972 1970-01-01 00:00:00
xx518388 1970-01-01 00:00:00
xx519494 2013-11-20 05:00:00
xx525723 1970-01-01 00:00:00
xx525730 1970-01-01 00:00:00
xx525787 1970-01-01 00:00:00
xx528536 1970-01-01 00:00:00
xx528542 1970-01-01 00:00:00
xx530465 1970-01-01 00:00:00
xx532486 1970-01-01 00:00:00
xx532492 1970-01-01 00:00:00
xx532556 1970-01-01 00:00:00
xx532595 1970-01-01 00:00:00
xx533669 1970-01-01 00:00:00

*Either does this which is even more puzzling considering the output above:*
# /opt/rt4/bin/rt ls "Queue = 'Blocks' AND Status != 'removed' AND
CF.{ServerClosed} < 'now' AND CF.{ServerClosed} != '1970-01-01 00:00:00'"
-f 'id,CF.{ServerClosed}'
*id CF.{ServerClosed}*
xx454020 1970-01-01 00:00:00
xx478114 1970-01-01 00:00:00
xx489293 1970-01-01 00:00:00
xx489299 1970-01-01 00:00:00
xx492598 1970-01-01 00:00:00
xx495471 1970-01-01 00:00:00
xx495481 1970-01-01 00:00:00
xx506972 1970-01-01 00:00:00
xx518388 1970-01-01 00:00:00
xx519494 2013-11-20 05:00:00
xx525723 1970-01-01 00:00:00
xx525730 1970-01-01 00:00:00
xx525787 1970-01-01 00:00:00
xx528536 1970-01-01 00:00:00
xx528542 1970-01-01 00:00:00
xx530465 1970-01-01 00:00:00
xx532486 1970-01-01 00:00:00
xx532492 1970-01-01 00:00:00
xx532556 1970-01-01 00:00:00
xx532595 1970-01-01 00:00:00

xx533669 1970-01-01 00:00:00

*I *can* find tickets where the value is set by doing this though:*
# /opt/rt4/bin/rt ls "Queue = 'Blocks' AND Status != 'removed' AND
CF.{ServerClosed} < 'now' AND CF.{ServerClosed} > '1970-01-01 00:00:00'" -f
'id,CF.{ServerClosed}'
*id CF.{ServerClosed}*
xx519494 2013-11-20 05:00:00

Is using > "1970-01-01 00:00:00” the only way?  Seems like that definite
value might be begging for bugs or something if somehow the unset date
equals "1970-01-01 0*5*:00:00” for some reason down the road.  I just want
to make sure I’m not setting myself up for buggy behaviour in the future.

-- 
Landon Stewart :: lstewart at iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20131120/eea31068/attachment.htm>