I have to find difference between 2 different date formats in new relic using NRQL.
The below query returns SELECT latest(expiresAt),latest(dateOf(timestamp)) FROM abc
2023-01-27,April 13, 2022 as output. Now i have to find number of days between these two dates of different format. How can i do that?
1 Answer
The native timestamp field in NR is a unix epoch timestamp in milliseconds. Assuming expiresAt is a custom field that you are sending, you should format it in the same unix milliseconds format as timestamp, that way you could calculate the # of days like this:
SELECT (latest(expiresAt) - latest(timestamp))/(1000*60*60*24) FROM abc
If that isn't possible, you could use Synthetics to create a scripted monitor that will run that query via API, return the timestamp/expiresAt values, and then convert both results using the native JS Date object to subtract and get the # of days.
