{"action":"opened","issue":{"url":"https://api.github.com/repos/grafana/grafana/issues/11972","repository_url":"https://api.github.com/repos/grafana/grafana","labels_url":"https://api.github.com/repos/grafana/grafana/issues/11972/labels{/name}","comments_url":"https://api.github.com/repos/grafana/grafana/issues/11972/comments","events_url":"https://api.github.com/repos/grafana/grafana/issues/11972/events","html_url":"https://github.com/grafana/grafana/issues/11972","id":324038798,"number":11972,"title":"MSSQL: ","user":{"login":"FatherTime1","id":15651375,"avatar_url":"https://avatars2.githubusercontent.com/u/15651375?v=4","gravatar_id":"","url":"https://api.github.com/users/FatherTime1","html_url":"https://github.com/FatherTime1","followers_url":"https://api.github.com/users/FatherTime1/followers","following_url":"https://api.github.com/users/FatherTime1/following{/other_user}","gists_url":"https://api.github.com/users/FatherTime1/gists{/gist_id}","starred_url":"https://api.github.com/users/FatherTime1/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/FatherTime1/subscriptions","organizations_url":"https://api.github.com/users/FatherTime1/orgs","repos_url":"https://api.github.com/users/FatherTime1/repos","events_url":"https://api.github.com/users/FatherTime1/events{/privacy}","received_events_url":"https://api.github.com/users/FatherTime1/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-05-17T14:06:04Z","updated_at":"2018-05-17T14:06:04Z","closed_at":null,"author_association":"NONE","body":"Version 5.1.2, MSSQL data source, Windows\r\n\r\nBefore this plugin I implemented the simple json interface against my data store. The query on that interface had some properties that helped in the calculation of what data to return. Specifically \r\n- Interval/IntervalMS\r\n- Max Data points\r\n- Range\r\n\r\nI found the _timeFrom() and _timeTo() macros to handle Range, but I do not see a way to get the interval, or the max data points.\r\n\r\nIt would be convenient for a macro to be able to supply the interval and/or max data points and maybe even update the timeGroup macro to be able to just take a dateColumn and then default to the correct interval based on the range. If valuable the generated Sql could also apply Max Data Points.\r\n\r\nI have been keeping my data in Unix Epoch form, and did not save it as a DateTime, In order to use the timeGroup macro, I had to add a date time column to my data.  For instance I had a simple query\r\n```\r\nSELECT\r\n  $__timeGroup(Time, '1440m') as time,  \r\n  avg(Value)\r\nFROM\r\n  Counter_Test\r\nWHERE\r\n  $__timeFilter(Time)\t  \r\nGROUP BY\r\n  $__timeGroup(Time, '1440m')\r\nORDER BY 1\r\n```\r\nThis is expanded to \r\n```\r\nSELECT\r\n  CAST(ROUND(DATEDIFF(second, '1970-01-01', Time)/86400.0, 0) as bigint)*86400 as time,\r\n  avg(Value)\r\nFROM\r\n  Counter_Test\r\nWHERE\r\n  Time >= DATEADD(s, 1523971929, '1970-01-01') AND Time <= DATEADD(s, 1526563929, '1970-01-01')\r\nGROUP BY\r\n  CAST(ROUND(DATEDIFF(second, '1970-01-01', Time)/86400.0, 0) as bigint)*86400\r\nORDER BY 1\r\n```\r\nI was wondering if a __timeGroupEpoch macro could be created _(Also same ideas about expanding the date range into the interval)_. Using the same Query as before except with $__timeGroupEpoch(TimeEpoch ) as time and $__unixEpochFilter(TimeEpoch ) it could expand to the following which I think might be more efficient\r\n```\r\nSELECT \r\n\t(TimeEpoch - (TimeEpoch % 86400)) time, \r\n\tAvg(Value) intervalValue \r\nFROM \r\n\tCounter_Test WITH (NOLOCK) \r\nWHERE \r\n\tTimeEpoch >= 1523972683 AND TimeEpoch <= 1526564683\t\t\r\nGROUP By \r\n\t(TimeEpoch - (TimeEpoch % 86400))\r\nORDER BY 1\r\n```"}}
{"actor":{"display_login":"FatherTime1"}}