How to find number of days between 2 dates in different format in NRQL?

Question

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?

Answer 1

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.