Question about NRQL and dates

Is there a way to to accomplish selecting only data where a date field is greater than 1 hour ago?

SELECT * FROM MyData WHERE my_date_field > 1 Hour ago EXTRAPOLATE

I know in MySQL and most other RDMS you can do something like

SELECT * FROM MyData  WHERE my_date_field > now()- interval 1 hour;

But I have not found a way to duplicate that behavior with NRQL. Does anyone have any way they are able to replicate the MySQL behavior?

Hi there @Richard.Corlew -

You can’t use the greater than operator with times in NRQL. That said, you can combine SINCE with UNTIL to select a time range, like SINCE last month UNTIL today. That kind of combo may get you closer to what you need?

Thanks for the pointer @hross but using SINCE uses the timestamp but we are needing to use a different date/time column for the time comparison. I have tried several different variations of SINCE and UNTIL but they all appear to rely on the timestamp of when the event was received and will not use any other column.

The following query returns 0 results when there should be between 30 - 40 results every hour.

SELECT * FROM MyData WHERE my_date_field  SINCE 1 Hour ago EXTRAPOLATE

@Richard.Corlew First, LOVE your profile pic. Classic!

Wondering if you were able to find another solution that does not rely on the timestamp? Please do share, if so :slight_smile:

If not, hoping that maybe other community members can give you some further advice.

Hi. Im keen to know if there are any options for this? We need to know if any file have been received into our system with a date of X, not based on the timestamp but based on the filelastwrite attribute. Any ideas on this?

Hi, @Brad.Nightingale1: You may be able to use the Flex integration to execute a command to get the file‘s LastWrite time and send the result to New Relic.

1 Like

yes thanks. I was trying to avoid having to do that in the flex integration script, but if thats the only way to do it then I will. Thanks again.

It‘s not the only way: if you prefer, you may write a custom integration. :slight_smile: I don‘t think New Relic has information about individual files by default.