Your data. Anywhere you go.

New Relic for iOS or Android


Download on the App Store    Android App on Google play


New Relic Insights App for iOS


Download on the App Store


Learn more

Close icon

Feature Idea: NRQL. dateOf(timestamp) ordered in alphabetical order

nrql
feature-idea

#1

We need to execute a NRQL query faceting by timestamp and then order the results.
Our query is the following:

SELECT count(*) from Transaction where appName = 'MY_APP' SINCE 1 month ago LIMIT 2000 FACET dateOf(timestamp), error

When we get the results and try to order by dateOf(timestamp) column we face that the order is alphabetical

  1. October 1, 2019
  2. October 10, 2019
  3. October 10, 2019
  4. October 11, 2019
  5. October 2, 2019

Thanks to support.newrelic.com I found that dateOf funtion returns a String so the default order is alphabetical. Knowing that, I found a workaround faceting by both timestamp and dateOf(timestamp) and then using timstamp for ordering:

SELECT count(*) from Transaction where appName = 'MY_APP' SINCE 1 month ago LIMIT 2000 FACET timestamp, dateOf(timestamp), error

I would like to suggest 2 improvements:

  1. since return type is relevant it could be usefull add it in the documentation. At the moment at https://docs.newrelic.com/docs/query-data/nrql-new-relic-query-language/nrql-query-examples/group-results-across-time for dateOf function it is specified the following: “Returns the date of the timestamp”: it seems it returns a date format;
  2. my workaround forced my to add a column just to make the sort work. It could be usefull to have function to format the timestamp in readable fomat.

THank you,

Marco


New Relic Edit

  • I want this too
  • I have more info to share (reply below)
  • I have a solution for this

0 voters

We take feature ideas seriously and our product managers review every one when plotting their roadmaps. However, there is no guarantee this feature will be implemented. This post ensures the idea is put on the table and discussed though. So please vote and share your extra details with our team.


#2

@Marco.Sigismondi - great ideas here - I’ve added a poll here for you - and we’ve got the feature ideas filed for you internally :smiley:


#3

Great feature request. It doesn’t help that the format is not picked up in Excel when the table is exported as a CSV.


#4

To make Excel load in some way dates I think that NRQL should provided some format function that let you define the output format.


#5

I just noticed a strange behavior in dates output: timestamp is formatted in milliseconds only if faceted by timestamp.

For example executing the following query

SELECT timestamp, dateOf(timestamp) from Transaction where appName = 'MY_APP' SINCE 1 month ago LIMIT 2

the CSV export produce this

Timestamp,Date Of Timestamp
2019-10-11 12:28:04,"October 11, 2019"
2019-10-11 12:28:04,"October 11, 2019"

If instead I execute the following

SELECT count(*) from Transaction where appName = 'MY_APP' SINCE 1 month ago LIMIT 10 facet timestamp, dateOf(timestamp)

the CSV export produce this

Timestamp,Date Of Timestamp,Transactions
1568367060588,"September 13, 2019",2
1570180914143,"October 4, 2019",2

The main difference is that the timestamp is printed in milliseconds if faceted while is printed as YYYY-MM-DD HH:MM:SS if not. The interesting thing is that is differente from tha output of dateOf and does not seems a string so it should be some kind of Date object.


#6

These is such helpful context! I have added your additional info to the Feature Request that was created for you. Thanks again! :blush: