This discussion is archived
5 Replies Latest reply: Nov 27, 2012 9:48 AM by Branchbird - Pat RSS

past 1 year/6 months data by having creation date in the meta data

664493 Newbie
Currently Being Moderated
Hi,

We have a creation date in our meta data. We would like to get past 6 months/1 year data. Is there a way we can write EQL like creation date<sysdate-180 ? or how do we acheive this.

Thanks,
Indhu
  • 1. Re: past 1 year/6 months data by having creation date in the meta data
    Branchbird - Pat Journeyer
    Currently Being Moderated
    Hi Indhu,

    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.

    Patrick Rafferty
    http://branchbird.com
  • 2. Re: past 1 year/6 months data by having creation date in the meta data
    920244 Newbie
    Currently Being Moderated
    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.

    However, writing

    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

    Hope this helps,

    Richard
  • 3. Re: past 1 year/6 months data by having creation date in the meta data
    Branchbird - Pat Journeyer
    Currently Being Moderated
    Richard,

    Good to know about the right side restriction being removed.

    One clarification:

    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.

    Patrick
  • 4. Re: past 1 year/6 months data by having creation date in the meta data
    664493 Newbie
    Currently Being Moderated
    Hi Patrick,

    We have the attribute creation_date in our meta data.We do not have any sysdate there. Do we need to load sysdate and then do a diff as you have suggested,

    DEFINE test AS SELECT
    creationdate AS creationdate ,
    sysdate AS CurrentDate,
    sysdate - creationdate AS Diff
    WHERE Diff < TO_DURATION('P180D');

    Please assist further.

    Thanks,
    Indhu
  • 5. Re: past 1 year/6 months data by having creation date in the meta data
    Branchbird - Pat Journeyer
    Currently Being Moderated
    Hi Indhu,

    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
    From AllBaseRecords
    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.

    Patrick
    http://branchbird.com

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points