Grafana: handy way to show historic data points

1

I am having at hand, a postgres database containing datasets collected some years ago. Here’s how the table looks like.

postgres=# select * from urbansense.new_table;  
+---------------------------+-----------------+------------------+-----------+
|         recvtime          |   entitytype    |     attrname     | attrvalue |
+---------------------------+-----------------+------------------+-----------+
| 20014-04-18T12:05:42.00Z  | WeatherObserved | illuminance      | 20        |
| 20014-04-18T12:05:42.00Z  | WeatherObserved | temperature      | 15.2      |
| 20014-04-18T12:05:42.00Z  | WeatherObserved | relativehumidity | 64.3      |
| ....                      | ......          | .....            | .....     |
| 2015-07-10T11:47:02+01:00 | WeatherObserved | illuminance      | 29562.7   |
| 2015-07-10T11:47:02+01:00 | WeatherObserved | temperature      | 20.7      |
| 2015-07-10T11:47:02+01:00 | WeatherObserved | relativeHumidity | 78.2      |
+---------------------------+-----------------+------------------+-----------+

The goal is to display data points for temperature (attrvalue) on grafana graph panel.

I can retrieve the values (to display on panel using the query below) from postgres client, but grafana shows Data points outside time range only:

postgres=# SELECT DISTINCT
  date_trunc('minute', to_timestamp(recvtime,'YYYY-MM-DD HH24:MI:SS')) AS "time",
  attrvalue::float AS temperature 
FROM
  urbansense.new_table 
WHERE  
  attrname = 'temperature' 
  AND attrvalue <> 'null' 
GROUP BY time, urbansense.new_table.attrvalue;
+------------------------+-------------+
|          time          | temperature |
+------------------------+-------------+
| 2015-07-10 07:49:00+00 | 24.1        |
| 2015-07-09 21:18:00+00 | 20.2        |
| 2015-07-09 23:52:00+00 | 19.8        |
| .....                  | ..          |
| 2015-07-08 23:56:00+00 | 29.1        |
| 2015-07-09 16:02:00+00 | 23.6        |
| 2015-07-09 09:30:00+00 | 32.3        |
| (31037 rows)           |             |
+------------------------+-------------+

I tried using the following query from grafana query editor again, (using $__timeFrom() ), grafana displays: No data points

SELECT 
        COUNT(DISTINCT urbansense.new_table.attrvalue) as time
        FROM 
        urbansense.new_table
        WHERE  
        attrvalue <> 'null'
        AND to_timestamp(recvtime,'YYYY-MM-DD HH24:MI:SS') > $__timeFrom()

What is the correct way to display data points within dates available in recvtime column only? I guess grafana is trying to display points in current time (or some few hour ago, so the first error Data points outside time range.

2

I am having at hand, a postgres database containing datasets collected
some years ago. Here’s how the table looks like. ```

I’ve picked out only the first column:

postgres=# select * from urbansense.new_table;
±--------------------------+

    recvtime          |

±--------------------------+

20014-04-18T12:05:42.00Z |
20014-04-18T12:05:42.00Z |
20014-04-18T12:05:42.00Z |
… |
2015-07-10T11:47:02+01:00 |
2015-07-10T11:47:02+01:00 |
2015-07-10T11:47:02+01:00 |
±--------------------------+

I’m seriously bothered by the format of those first three lines (I assume you
did copy and paste the data, so it’s not a typo on your part)…

Antony.

3

I can confirm the values begin like that, then changes to the format as I checked with first few rows:

+---------------------------+-----------------+------------------+-----------+
|         recvtime          |   entitytype    |     attrname     | attrvalue |
+---------------------------+-----------------+------------------+-----------+
| ...                       | ...             | ...              | ...       |
| 2010-04-18T12:05:42.00Z   | WeatherObserved | relativeHumidity | 41        |
| 2010-04-18T12:05:42.00Z   | WeatherObserved | illuminance      | null      |
| 2015-07-08T21:56:50+01:00 | WeatherObserved | temperature      | 28.1      |
| 2015-07-08T21:56:50+01:00 | WeatherObserved | relativeHumidity | 49.8      |
+---------------------------+-----------------+------------------+-----------+
(70 rows)
4

Isn’t it possible to specify time interval so grafana looks for data within the interval only? This No Data points and Data points outside time range messages are frustrating and helpless!