What’s the correct SQL syntax for a date/time custom field that’s not set?

RT Users
1

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 05: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@iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932

2

Is using > "1970-01-01 00:00:00” the only way?

Currently, yes.

Seems like that definite value might be begging for bugs or something
if somehow the unset date equals "1970-01-01 05: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.

That magic date is the “UNIX epoch,” and will not change.

  • Alex
3

Is using > "1970-01-01 00:00:00” the only way?

Currently, yes.

Ok thank you.

Seems like that definite value might be begging for bugs or something
if somehow the unset date equals "1970-01-01 05: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.

That magic date is the “UNIX epoch,” and will not change.

  • Alex

Oh I knew it was the Unix epoch date/time but I was concerned that maybe if
the server’s timezone changed it could somehow offset the epoch. I guess
since that’s simply the ‘display’ version of null then it’s safe to use it
as a constant.

Anyway thanks again.

Landon Stewart :: lstewart@iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932