Interesting how this $__time(your timestamp column) works.
Using data table panel and Grafana5.x + datasource Postgre.
select $__time(t.ts), t.ts as “time”, t.ts as timeraw
Table panel display time as:
2017-01-03 07:38:30, 1970-01-18 06:03:41,2017-01-03T05:38:30.007394Z
So the time macro row conversion is correct. The middle one is “bad”.
By looking the generated SQL:
select t.ts AS “time”, t.ts as “time”, t.ts as timeraw,
There seems to be extra magic behind the scenes for this macro. Due that “select time as “time”” will not do same as using macro.
Hi,
I wonder if this is because you end up with selecting two columns with the same alias, i.e. time. If you changed to this instead, what happens then?
select $__time(t.ts), t.ts as time2, t.ts as timeraw
Marcus
Same wrong date. If you write ‘as “time”’ without extra column same name, its the same result. I do not have issue, happy to use Macro. However, I was wondering this for quite some time, because I wrote “as ‘time’” without Macro… as I have done earlier with MySQL datasource plugin.
…and now I found out, that there is some inconistency how grafana displays (does the conversion) time column.
Case 1, Two columns of “as time”, other one macro
SQL:select $__time(t.ts), t.ts as time, t.val as value, t.tag_id, t.validity
Output: 2017-01-03 07:38:30 (Correct!), 1970-01-18 06:03:41
Case 2. One column “as time” with macro
SQL: select $__time(t.ts), t.val as value, t.tag_id, t.validity
Output: 1970-01-18 06:03:41
Case 3. One column “as time” without macro
SQL: select t.ts as “time”, t.val as value, t.tag_id, t.validity
Output: 1970-01-18 06:03:41
…post edit…
It’s not macro related issue:
Case 4. Two ‘as time’ columns, without macro
SQL: select t.ts as time, ‘extra’ as time, t.val as value, t.tag_id, t.validity
Output: 2017-01-03 07:38:30 (Correct!), Invalid date
I seemed to be lucky to get it working, somehow adding double column “time” will affect to timestamp conversion to work with macro?!
I think that Grafana per default adds a column style for column named Time when using the table panel. For Grafana to be able to parse a column as data it must be in epoch format.
So if you use $__timeEpoch macro instead of $__time you should get back a timestamp in epoch format and it should display correctly.
If you still use the $__time macro you can remove the column style that applies to the Time column and/or use type string instead and that should also render a correct date/not 1970-01-01.
One important thing is that you shouldn’t have more than one column with the same name.
Marcus
I tried epoch macro also: it will not work either. It will display wrong date (conversion) 1970-01-18 06:03:41
Also I tried to convert postgres timestamp to first epoch… but this did not work, macro handling issue?:
select $__timeEpoch(extract(epoch from t.ts)), t.ts as time, ‘extra’ as time, t.val as val
error -> pq: syntax error at or near “as”
It was accident, that I got conversion working with double time column 
yeah, bad SQL syntax.
Also, this default “time” column to date conversion is reaaaally handy. End users want to see local time, clean date.
Btw, Great work on Grafana 5.x. 
Back to this. Yes!, casting Postgre timestamp to text works in case of ‘table panel’. Like this as you said:
select t.ts::text as time, t.val as value, ‘kukkuu’ as metric
Thanks for the tip!
Maybe add this to documentation or something, by default table panel adds column style for ‘time’. Typically postgre DB column data type is timestamp (with or without timezone) → and it will be displayed wrong by default.
Not sure if this will help. I have been trying to get Grafana to match my Postgres data. It was confusing. My project is new so that I was able to go back and change the code to get it to work.
My understanding and what I did…
I changed Grafana timezone setting to ‘Local browser time’. I was ‘inserting’ data as type TIMESTAMP (using Go, but that doesn’t matter as we are interested in how Postgres stores and retrieves data).
Postgres stores date/time data as UTC, but displays it via SELECT as the local time zone. I suspect Grafana knows it is stored as UTC and thus uses that value (but doesn’t assume that the graphic display timezone setting should correspond.)
So…I changed the the insertion type to TIMEZONETZ, which stores the time zone (local) and voila, Grafana now displays the data as my local timezone. It still shows the data/time in the query as UTC but displays correctly for all levels (5mins,1hour, 2days and so on…).
Hope this saves someone some time! 
ps. I realize that if you are not collecting the data yourself, or cannot change tons of data this may not be a viable solution for you. But at the outset if you realize this…
1 Like
