When using PostgreSQL as a datasource, I’m only able to use “half” of the graph space. The graph times (x-axis) extend 6 hours into the future. This is also my offset from UTC. I’ve attempted to change the timezone at the PostgreSQL server level (i.e., for clients via postgresql.conf) and to set the timezone for the Grafana user accessing the DB (via alter user foo set timezone 'Etc/UTC' – and to other TZ’s). None of these have been effective.
Allegedly, PostgreSQL stores “timestamp” fields (auto-populates with current_timestamp upon insert) in UTC. Between Google and tinkering, I think that I’ve suffered enough to ask for help. I hope that someone else encountered this problem (er, and resolved it). Ah, I should mention that the dashboard is set to UTC (ah, which explains the “half-graph” bit). The underlying problem, then, is that I simply cannot seem to extract the correct timestamp data from the database.
I’m using PostgreSQL 9.2.18 and grafana-4.3.1-1.x86_64 on RHEL7.3.
Can you give an example of the queries you are running and some sample values that are returned
Select
(extract(epoch from “idts”)/extract(epoch from ‘5m’::interval))::intextract(epoch from ‘5m’::interval) as time,
bytes_written,
device_name as metric
FROM device_list
WHERE extract(epoch from idts) BETWEEN 1520488800 AND 1520575199 and device_type like '%Disk%'
group by (extract(epoch from “idts”)/extract(epoch from ‘5m’::interval))::intextract(epoch from ‘5m’::interval), device_name, bytes_written
order by device_name, (extract(epoch from “idts”)/extract(epoch from ‘5m’::interval))::int*extract(epoch from ‘5m’::interval) desc
… and I see that it’s storing the value as a local time.
time | bytes_written | metric
------------±--------------±--------------------------------
1520506500 | 1072819769344 | /dev/mapper/st3diskcachepool0-0
1520506200 | 1072765923328 | /dev/mapper/st3diskcachepool0-0
1520505900 | 1072765923328 | /dev/mapper/st3diskcachepool0-0
I expect this to be in UTC (not local time; I’ve read that PostgreSQL stores all timestamps as UTC, internally). However, I simply haven’t been able to figure-out how to get Grafana to interpret these values as local time(s).
Here’s the Grafana statement (not the generated SQL):
SELECT
idts as time,
bytes_read,
device_name as metric
FROM device_list
WHERE $__timeFilter(idts) and device_type like '%Disk%'
group by idts, device_name, bytes_read
order by device_name, idts desc
Thanks!
Please note that when I do a much simpler, direct SQL query, I see the following:
select idts as time, extract(epoch from idts) as epoch, bytes_read, device_name as metric FROM device_list where device_type like ‘%Disk%’ order by idts desc limit 3;
time | epoch | bytes_read | metric
----------------------------±-----------------±--------------±--------------------------------
2018-03-08 11:05:24.957711 | 1520507124.95771 | 466431352832 | /dev/mapper/st6diskcachepool7-1
2018-03-08 11:05:24.957448 | 1520507124.95745 | 455331426304 | /dev/mapper/st6diskcachepool6-1
2018-03-08 11:05:24.957447 | 1520507124.95745 | 5249125720064 | /dev/mapper/st6diskcachepool8-0
(3 rows)
It LOOKS/SEEMS like the correct data is being returned… but that I’m messing-up the display of those data in the dashboard. As little as I like InfluxDB, I haven’t experienced these issues with InfluxDB. I.e., I’m able to correctly display data from other datasource types. Dunno – which is why I’m asking for help. Cheers.
Unix Timestamp is UTC so if your queries return unix timestamps all timezone information you had will be gone and the time will be converted to UTC if you are using timestamptz. What datatype is idts? Are you using timestamptz?
You can force postgres to treat data as being in a certain timezone if you are not using timestamptz
Something like the following query
SELECT
idts AT TIME ZONE 'GMT+1' as time,
bytes_read,
device_name as metric
FROM device_list
WHERE $__timeFilter(idts AT TIME ZONE 'GMT+1' ) and device_type like '%Disk%'
group by idts, device_name, bytes_read
order by device_name, idts desc
I wasn’t able to use “timestampz.” This doesn’t appear to be a valid datatype. However, your feedback did lead me to a solution. Rather than storing “current_time,” I’ve modified all tables to store a UTC timestamp. This is the DDL I used to accomplish this: "idts timestamp without time zone default (now() at time zone ‘utc’ " (note, that is the DDL for a single column in a PostgreSQL table).
With the timestamp in UTC in the database, it works. Surely there’s an easier way? I mean… cannot Grafana properly display local timestamps? I “fixed” my data (only a few days of test data) by creating a copy of each table (with the new idts UTC definition) and “selecting into” it from the source table. I then updated the data by adding 6 hours to each record (this is my UTC offset).
This will be a bit of a hassle for my older data (currently in InfluxDB… which I need to migrate to PostgreSQL). Still, at least I have a work-around to what seems like a bug in Grafana. I don’t know. I only know that I appreciate the help – MANY THANKS!
If you use timestamptz (timestamp with timezone) or store your time in a timestamp without timezone as utc everything works without problems. Why couldnt you use timestamptz?
I don’t believe that “timestampz” is valid for my version of PostgreSQL. Here’s what happens…
asdf=# create table foobar (foo varchar(20), bar timestampz);
ERROR: type “timestampz” does not exist
LINE 1: create table foobar (foo varchar(20), bar timestampz);
^
asdf=# create table foobar (foo varchar(20), bar timestamp);
CREATE TABLE
asdf=# \d foobar
Table "public.foobar"
Column | Type | Modifiers
--------±----------------------------±----------
foo | character varying(20) |
bar | timestamp without time zone |
Oh you forgot a t in the datatype name its timestamp + tz for timezone
postgres=# create table foobar(foo text, bar timestamptz);
CREATE TABLE
You are correct. Thanks for the correction. I did a simple insert… and it does indeed have a UTC timestamp. Cheers!
Ah, the fine print: “The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. (Releases prior to 7.3 treated it as timestamp with time zone.) timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.”
I didn’t see that, initially, here: https://www.postgresql.org/docs/9.1/static/datatype-datetime.html
