How to get the data from previous week in a lookup

[lucas4394]

Hi. I have a lookup with a timestamp field, myTime, how can I put all the data from the previous week based on the current time against myTime?

For instance, current date is August 12, 2019.

time         col_2
----------   ---------------
2019-08-07   Yay!
2019-07-05   Nay!

In this case, the first record is the expected result. Any clues? Thanks.

    time         col_2
    ----------   ---------------
    2019-08-07   Yay!

[marycordova]

convert the time in the lookup to epoch and use math to do the "now minus 1 week" calculation

base search that makes the lookup
|convert mktime(myTime)

myTime            now             yay/nay
-------------     ----------      -------
1,564,439,062     1565648785      nay      
1,565,043,862     1565648785      yay

base search that uses the lookup later
| where 'myTime'>=now()-604800

*this is pseudo code/spl...some syntax fixing might be necessary

[lucas4394]

Hi marycordova, thank you for your inputs. From your query, some of the days from previous week are excluded. Here is my workaround:

 base search that makes the lookup
 |convert mktime(myTime)
 | eval first_day_last_week=relative_time(now(),"-1w@w"), last_day_last_week=relative_time(relative_time(now(),"-1w@w"),"+7d@d")
| where myTime>=first_day_last_week AND myTime<=last_day_last_week