publish yesterday's data from today's date

[martin61]

I want to get QID list from yesterday’s published data.  For that I'm using PUBLISHED_DATETIME field with yesterday’s date. The date format for that field result is in GMT format (2005-11-11T08:00:00Z). For example, I’m running this search on may 4th, but I need to get QID fields with published date as 05/03/2022. (May 3rd)

|table QID PUBLISHED_DATETIME

[bowesmana]

You could either do a simple string match where clause on the PUBLISHED_DATETIME field or comvert it to epoch and compare.

| where match(PUBLISHED_DATETIME, "^2022-05-03")

of course if you want to compare always against yesterday, then you would need to make a field with yesterday's date. Here's an example that creates some dummy dates and then searches for yesterday

| makeresults count=20
| eval today=strftime(now(), "%Y-%m-")
| eval day=tonumber(strftime(now(), "%d")) - 1
| eval PUBLISHED_DATETIME=today."0".(random() % day + 1)
| eval yesterday=strftime(relative_time(now(), "-d@d"), "^%F")
| where match(PUBLISHED_DATETIME, yesterday)

So, you need to format a string like eval yesterday=... and then the where clause will string match.

Alternatively you can do with with epoch comparison with numeric comparison after parsing the PUBLISHED_DATETIME and calculating start end end times for yesterday, e.g. 

| makeresults count=20
| eval today=strftime(now(), "%Y-%m-")
| eval day=tonumber(strftime(now(), "%d")) - 1
| eval PUBLISHED_DATETIME=today."0".(random() % day + 1)."T08:00:00Z"
| eval pdate=strptime(PUBLISHED_DATETIME,"%FT%TZ")
| eval yesterday_start=relative_time(now(), "-d@d")
| eval yesterday_end=relative_time(now(), "@d")
| where pdate>=yesterday_start AND pdate<yesterday_end

You can run both of these example searches that will show you how it works