You're definitely on the right track here. Anytime that I need to leverage a difference between two attribute values (amounts, dates, etc.) in a where clause, I do something like this:
DEFINE test AS SELECT
DaysSinceEpoch1 AS TransactionDate,
TodayDaysSinceEpoch AS CurrentDate,
TodayDaysSinceEpoch - DaysSinceEpoch1 AS Diff,
TransactionId As TransactionId,
FormattedDate As NicelyFormattedDate
WHERE Diff < 180;
Basically, you "promote" the difference between the current date and the date in your fact table to an attribute using EQL and then use that as the comparison value. In the current version (EID 2.3), you can only have constants on the right side of a comparison (=, <, <=, etc.) in EQL so this is the way you work around it.
Hope that helps, if any of this is unclear, feel free to post/send your EQL and I can take a look.
Actually, we removed the restriction on comparison operators in EID 2.3; you may now use an arbitrary expression on either side of these operators.
WHERE TodayDaysSinceEpoch - DaysSinceEpoch1 < 180
results in a type error, because the difference of two DateTime values is a Duration, not an integer. You'll need to use something like the following instead:
WHERE TodayDaysSinceEpoch - DaysSinceEpoch1 < TO_DURATION('P180D')
The format for duration literals in EQL is the same as it is for ingest; you can find this described in the docs at http://docs.oracle.com/cd/E35822_01/server.740/es_data_loading/toc.htm#duration%20property
Good to know about the right side restriction being removed.
The statement I referenced above would have DaysSinceEpoch as an integer, not a duration/datetime per the "usual magic" that is traditionally done to enrich dates on an implementation prior to the introduction of "date math" in 2.3. For example, today (11/16/2012) being 15,561 days since epoch minus whatever transaction date (expressed in # of days since epoch) is less than 180. I should've been more explicit in my typing.
Yes, I was referencing the common pattern where you load in Date Records into the index to assist with your calculations. The LQL below is really just "pseudocode".
For example, if you had an attribute called RecordType with 2 values: Calendar and RealData where all the actual records are tagged with RecordType=RealData and you have a single record in the index that is tagged with RecordType=Calendar.
You could then write something like this (again, it's psedudocode...this may not compile):
DEFINE getTheSysDate AS SELECT
ADateYouLoadInYourPipeline as sysdate
WHERE RecordType = 'Calendar';
DEFINE test AS SELECT
creationdate AS creationdate ,
getTheSysDate.sysdate AS CurrentDate,
CurrentDate - creationdate AS Diff
WHERE Diff < TO_DURATION('P180D');
You could also use the SYSDATE,SYSTIMESTAMP,CURRENT_DATE and CURRENT_TIMESTAMP functions if you are using the latest version of Endeca Server. The above way of loading Calendar records is kind of the "old" method. If you've never used the old method, the new one might be a better fit, I almost always load Calendar records reflexively because it's what I've always done.