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

[Landon Stewart]

On 20 November 2013 11:29, Alex Vandiver  wrote:

> On Wed, 2013-11-20 at 11:13 -0800, Landon Stewart wrote:
>
> > 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 :: lstew...@iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932

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

[Alex Vandiver]

On Wed, 2013-11-20 at 11:13 -0800, Landon Stewart wrote:

> 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

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