How to filter records which are NOT updated since last 24 hours?

I have set of records which gets updated frequently (every hour per day). What I want is, show me the records which are not updated since last 24 hours? Thus, I can quickly identify records out of many.

Query:

SELECT latest(status), latest(timestamp)
FROM 'tceRiRic:menuDeltaSync'
WHERE tenantName='Smashburger'
FACET storeNumber SINCE 3 weeks ago LIMIT MAX

Result: I just want highlighted records.

Attempts:

  1. I see that I have dateOf(timestamp) option but how can I compare with current time? i.e. I want to do something like NOW - dateOf(timestamp) > 24 hrs. If this is not possible the what are the other options?
  2. If I use SINCE 3 weeks ago UNTIL 24 hour ago LIMIT MAX then this gives me 24 hours older records which is also not desired.

Hi @gparmar

What you want to do is not possible with basic NRQL tools

However, we can try get close.

With your query you will get all of the results you see in your screenshot.

SELECT latest(status), latest(timestamp)
FROM 'tceRiRic:menuDeltaSync'
WHERE tenantName='Smashburger'
FACET storeNumber SINCE 3 weeks ago LIMIT MAX

There is no way to only get those with a timestamp of > 24hrs ago, however, you can order your query results by the minimum timestamp, to always surface the oldest records to the top of the results set.

SELECT latest(status), latest(timestamp)
FROM 'tceRiRic:menuDeltaSync'
WHERE tenantName='Smashburger'
FACET storeNumber ORDER BY min(timestamp) SINCE 3 weeks ago LIMIT MAX

Hope this get’s you a little closer to at least making these results more visible.

1 Like