Half-graph utilization with PostgreSQL timestamps

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

(extract(epoch from “idts”)/extract(epoch from ‘5m’::interval))::intextract(epoch from ‘5m’::interval) as time,
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))::int
extract(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):

idts as time,
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


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

idts AT TIME ZONE 'GMT+1' as time,
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);
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);

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