Built-in Grafana time macros not working in Postgres queries

Grafana PostgreSQL
1

Using Grafana v5.1.4 (a5fe24fcc) and a Postgres data source:

This query works:

SELECT
  EXTRACT(EPOCH FROM date_trunc('week', to_timestamp((json->'fields'->>'dateCreated')::int) AT TIME ZONE 'UTC')::date) AS time,
  1 as value
FROM
  phab_engineering_tasks
WHERE
  (json->'fields'->>'dateCreated')::int >= $__unixEpochFrom() AND (json->'fields'->>'dateCreated')::int <= $__unixEpochTo()
GROUP BY
  time

However this one doesn’t work (“Unknown macro __timeSec” error):

SELECT
  $__timeSec(date_trunc('week', to_timestamp((json->'fields'->>'dateCreated')::int) AT TIME ZONE 'UTC')::date),
  1 as value
FROM
  phab_engineering_tasks
WHERE
  (json->'fields'->>'dateCreated')::int >= $__unixEpochFrom() AND (json->'fields'->>'dateCreated')::int <= $__unixEpochTo()
GROUP BY
  time

And this one doesn’t either (“pq: syntax error at or near “GROUP””):

SELECT
  EXTRACT(EPOCH FROM date_trunc('week', to_timestamp((json->'fields'->>'dateCreated')::int) AT TIME ZONE 'UTC')::date) AS time,
  1 as value
FROM
  phab_engineering_tasks
WHERE
  $__unixEpochFilter((json->'fields'->>'dateCreated')::int)
GROUP BY
  time
2

There is no macro named $__timeSec you probably meant $__timeEpoch. At the moment grafana does not allow arbitrarily complex expression as macro arguments. Macro arguments must not contain , as that is used as separator for macro arguments.

Can you show the expanded query for your 3rd example. There is a button called ‘Generated SQL’ in the query editor that shows the expanded query.

3

There is no macro named $__timeSec

From http://docs.grafana.org/features/datasources/postgres/:slight_smile:

$__timeSec(dateColumn)	Will be replaced by an expression to rename the column to time and converting the value to unix timestamp. For example, extract(epoch from dateColumn) as time

Can you show the expanded query for your 3rd example.

image
image2364×844 186 KB

xhrStatus:"complete"
request:Object
method:"POST"
url:"api/tsdb/query"
data:Object
from:"1529938848092"
to:"1530543648092"
queries:Array[1]
0:Object
refId:"A"
intervalMs:600000
maxDataPoints:815
datasourceId:3
rawSql:"SELECT EXTRACT(EPOCH FROM date_trunc('week', to_timestamp((json->'fields'->>'dateCreated')::int) AT TIME ZONE 'UTC')::date) AS time, 1 as value FROM phab_engineering_tasks WHERE $__unixEpochFilter((json->'fields'->>'dateCreated')::int) GROUP BY time"
format:"table"
response:Object
results:Object
A:Object
error:"pq: syntax error at or near "GROUP""
refId:"A"
meta:Object
sql:"SELECT EXTRACT(EPOCH FROM date_trunc('week', to_timestamp((json->'fields'->>'dateCreated')::int) AT TIME ZONE 'UTC')::date) AS time, 1 as value FROM phab_engineering_tasks WHERE (json->'fields'->>'dateCreated' >= 1529938848 AND (json->'fields'->>'dateCreated' <= 1530543648::int) GROUP BY time"
series:null
tables:null
message:"pq: syntax error at or near "GROUP""
4

Looks like the parens are not properly closed in the generated sql. The following query should work though:

SELECT
  EXTRACT(EPOCH FROM date_trunc('week', to_timestamp((json->'fields'->>'dateCreated')::int) AT TIME ZONE 'UTC')::date) AS time,
  1 as value
FROM
  phab_engineering_tasks
WHERE
  (json->'fields'->>'dateCreated')::int BETWEEN $__unixEpochFrom() AND $__unixEpochTo()
GROUP BY
  time