NOTE: In this example, I will be using LOCAL dates – date fields in the time zone of the facility as opposed to dates in UTC. Because of that, I will be using sysdatetz function with an explicit time zone passed in as a parameter. If you are to use the non-local date (which is in UTC, like ORDER_ADDED_DATE), then you would need to use sysdate function. Please note the difference between the date fields ORDER_ADDED_DATE VS ORDER_ADDED_DATE_LOCAL:
We need to come up with YESTERDAY 19 and TODAY 19 (to make it dynamic, you can even leverage the parameter functionality). For this, I will hard-code 19:00:00 as the time.
- Filters Stage – I will use an advanced condition. The reason I am doing sysdatetz(-2,Europe/London) is to include orders from yesterday even when current time will be today after 19.
-
Display Fields (step 1) – create YESTERDAY and TODAY – similar to trunc(sysdate). This is short-hand way to extract just the date portion from a datetime field and convert it to string:
i. YESTERDAY
substring(sysdatetz(-1,Europe/London),0,10)
ii. TODAY
substring(sysdatetz(0,Europe/London),0,10) -
Display Fields (step 2) – create YESTERDAY 19:00:00 and TODAY 19:00:00 as dates
i. YESTERDAY
toDate(concatenate(YESTERDAY_LONDON," 19:00:00"))
ii. TODAY
toDate(concatenate(TODAY_LONDON," 19:00:00")) -
Display Fields (step 3) – find if a specific date field (I will be using ORDER_ADDED_LOCAL) is between YESTERDAY and TODAY:
condition(and(greaterThanOrEqual(ORDER_ADDED_DATE_LOCAL, YESTERDAY_LONDON),lessThan(ORDER_ADDED_DATE_LOCAL,TODAY_LONDON)),true,false)
- Filters Stage – Remove the records that are outside the range:
- Display Fields – Finally, you can arrange the fields in the order you prefer, and discard the calculated fields: IS_WITHIN_THE_RANGE, YESTERDAY_LONDON, TODAY_LONDON.