Using hourof(timestamp) giving unexpected results

Hi,

I am trying to get results for specific time period using hourof(timestamp).

SELECT timestamp, hourOf(timestamp),event_received from Metric where metricName = 'event_received' and EventType = 'TimeBlockChanged' SINCE 1 day ago WITH TIMEZONE 'America/Los_Angeles'

When using the above query , I am getting results for all timestamps. However, when I try to query for a specific timestamp, I am not getting the desired results.

SELECT sum( event_received) from Metric where metricName = 'event_received' and EventType = 'WorkerChanged' and hourOf(timestamp) = '2:00' SINCE 1 day ago WITH TIMEZONE 'America/Los_Angeles

In the first query, it clearly seen , that there are events for hourof(timestamp)=‘2:00’ . However, they are not visible when I try to query for that specific time.

Could some one help

I’m curious of your need to submit a custom metric and then treat it like events (instead of metrics).

Try adding the RAW keyword on the query. You can also just scope your time to the hour you want.

@6MM My actual use case is basically to set an alert for a specific time period as my job runs only once a day, and new relic has a time duration limitation of 2 hours, I have posted the issue here:


I am not sure what you mean by treating it as Events?
The ‘EventType’ here is basically a custom that we are sending since there are multiple types.

SELECT sum( APP02438.prod.event_received) from Metric where metricName = 'APP02438.prod.event_received' and EventType = 'WorkerChanged' and hourOf(timestamp) >= '6:00' and hourOf(timestamp) <= '11:00' SINCE 1 day ago WITH TIMEZONE 'America/Los_Angeles'

I am trying to figure out if I can evade that 2 hour time duration restriction.

If you have control over the data you are sending perhaps send an a custom event instead of a metric and include a status and time in the event. Or you can do something like this:

SELECT count( *) from MyCustomEventType where ENV = Prod and MyEvent = 'APP02438' and hourOf(timestamp) = '2:00' and timeblockchangedcount > 2

Then alert if you have a count over 0 at any time.