Hi, I have a custom event that holds data for a an order. It has a field ‘deliverydate’ that is an epoch timestamp. I want to write an nrql query to count unique orders WHERE deliverydate = today. Is that possible? I have not been able to figure our the WHERE part, how can i compare dateOf(deliverydate) with todays date?
Thanks!
Hi there @johan.nordanfors2 -
AFAIK, it’s not possible to construct the query as you describe user deliverydate=today, for example. BUT, you can append SINCE to the end of your query to look for events that occurred in a time range. It looks like this:
You can combine SINCE with UNTIL to select a time range, like SINCE last month UNTIL today.
COMPARE WITH might also be useful to you: NRQL syntax, clauses, and functions | New Relic Documentation
Let me know if that does not get you pointed in the right direction.
Thanks for you reply. Unfortunately that does not solve my issue. The SINCE statement defines a time interval that specifically operate on the event timestamp afaik, not on arbitrary attributes.
Lets say our orderevent looks like this
OrderEvent{
timestamp
ordercode(unique)
eventtype(confirmed,picked,shipped,completed)
deliverydate(can be 8 days forward)
ordervalue
}
I can easily find orders created today by: SELECT UniqueCount(ordercode) WHERE eventtype = ‘confirmed’ SINCE today
But for finding what orders are due for delivery tomorrow I’d need something like:
SELECT UniqueCount(ordercode) WHERE dateOf(deliverydate) = tomorrow SINCE 8 days ago
(How many orders have been placed since last 8 days towards delivery tomorrow)
So in order to set up a dashboard that for instance display the amount of orders due for delivery ‘tomorrow’ would require the WHERE statement to allow similar date functions as SINCE. That way, we can do comparisons, test for equality etc. on epoch timestamps for arbitrary attributes.
Does that make any sense? If yes, would be happy if you could make it a feature request. Maybe others think it would be a good idea to be able to use conditional statements based on time or date.
Hi Johan, did you find any way to fix this?
I am having a very similar problem
No, unfortunately I did not find a way to solve it with a nrql. I got the advice to build a custom app to visualize my data as apps are much more flexible in terms of what you can do with the data. I haven’t found the time to do it yet though. :-/
Hello @johan.nordanfors2 - Sorry that I wasn’t able to solve this for you, but I would love to hear how it goes with the app!
hi @johan.nordanfors2 , @dalvarez2 ,
Would like to check if you have found some solution for your issue.
I have got some similar requirement where we want to ran a query with created date = Today, but I don’t see an option as Today.
Hi there @Kalyani.Malyala -
You are correct - there is no TODAY option. Could you try SINCE 25 hours ago UNTIL 1 hour ago?
From the docs:
We support the following relative time ranges:
YESTERDAY,TODAY,SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY. For example,SINCE YESTERDAY UNTIL NOW.
But WHERE createdDate = Today is not possible.
Oh thanks @philweber - I did not turn that up. My search skills failed me! Appreciate you getting the right information out there!
No progress, have not had the time to dive in to app building, bit of a learning curve there. Limping along with what we have right now.
Not very sure if this could help you, but you can try creating a synthetic monitor in which you get the values required from NRQL in the monitor and the do the required maths and if the result is of your liking then throw an alert to notify you.
