Convert date format in data links

Grafana Data Links
1

Hi Community,

First to set the context, I’m using Grafana OSS 11.0.0 in a docker container.

MY AIM
In a dashboard, I have a list of events stored in a Postgres table. Those events are simply a list of:

  • “start” TIMESTAMP
  • “stop” TIMESTAMP
  • “title” TEXT
  • “tags” TEXT

image
image808×146 8.14 KB

To each line of the table, I’d like to apply a “Data link” to make the dashboard zoom in on the event time window. So I define my “Data Link” as
http://localhost:3000/d/be14qv4sb5qf4d/new-dashboard?orgId=1&from=${Date.parse(__data.fields.start)}&to=${__data.fields.stop:date}

WHAT HAPPENS
When clicking on the event, the dashboard time window does not change, because the &to= and &from= appear as formatted datetimes YYYY-MM-DDThh:mm:ss.xxxZ instead of a timestamp.

WHAT I TRIED
I tried then to create a variable in the query by forcing the date to timestamp.

SELECT
  start,
  stop,
  title,
  tags,
  CAST(start as TIMESTAMP) ts0,
  CAST(stop as TIMESTAMP) ts1
FROM
  site_events

No change. The “timestamped” dates were reconverted to standard dates.

QUESTIONS
a) Is there a way to enforce the dates to appear as timestamps in the data link?
b) Is there another way to do what I’m aiming to do?

Thanks for your feedbacks.

2

By further browsing (and testing) the subject, it appears that using

http://localhost:3000/d/be14qv4sb5qf4d/new-dashboard?orgId=1&from=${__data.fields.start:date:<format>}&to=${__data.fields.stop:date:<format>}

does actually change the date format.
The format is based on moment.js library definition Moment.js | Docs

That library supposedly allows to convert dates back to timestamp using date:x, but only from version 2.8.4. Since it’s not working, I guess Grafana 11 uses an older version. Any mean to update the version? Or any mean to override the restriction?

3

Don’t cast it to timestamp, but to int (maybe string) with SQL or with transformation. You may complain then that you don’t have human readable format → just select it 2 times (as timestamp AND as int) and then use int format in datalink and timestamp in visual presentation.

4

@jangaraj Thanks for the feedback

I tried

SELECT
  start,
  stop,
  title,
  tags,
  CAST(start as BIGINT) ts0,
  CAST(stop as BIGINT) ts1
FROM
  site_events
LIMIT
  50

but the panel did throw an error

image
image783×337 19.9 KB

5

it tells you clearly what the issue is.

try to use this

http://localhost:3000/${__value.raw}

image
image818×743 35.7 KB

please read the docu for data links

6

ON @yosiasz 's response
Actually, the dates in ‘start’ and ‘stop’ are TIMESTAMP data, but for any reason, Grafana converts them automatically to DATETIME as per system format.
I previously had a look at link to documentation you sent, but, at least for me, the explanations remain unclear. I made several attempts to combine the syntax, but nothing worked out properly.

What I understand based on your answer is “You need to create a variable ‘value’ that concatenates the time range with all ancillary symbols & and ?, and then use the raw values”.

@yosiasz I would be grateful if you can provide more specific insights on the proposed solution.

ON MY PREVIOUS ERROR
After digging a bit, it appears that applying a CAST from TIMESTAMP to INTEGER is no longer allowed as per newer Postgres syntax. It applies only to old Postgres versions (up to which one I couldn’t figure out). I’m currently using version 16, so no way to have it working.

SOME OTHER ATTEMPTS
I tried to create 2 queries:

  • QUERY A
SELECT start, stop, title, tags FROM site_events LIMIT 50
  • QUERY B
SELECT EXTRACT(EPOCH from start)*1000 ts0, EXTRACT(EPOCH from stop)*1000 ts1 FROM site_events LIMIT 50

and changed my Data Link to

http://localhost:3000/d/be14qv4sb5qf4d/new-dashboard?orgId=1&from=${__data.fields.ts0}&to=${__data.fields.ts1}

When both queries are activated, QUERY A is displayed, but QUERY B is not taken into account, and so the link does not work.

A SUCCESSFUL ATTEMPT, BUT STILL NOT SATISFACTORY
By merging queries A and B, it’s working. However now, I couldn’t figure out how to have ‘ts0’ and ts1’ to disappear from the table display. I played with the manual column width selector, but it rather looks like a “get around”.

1 Like
7

if you want to hide it look into this solution

1 Like
8

Of course, implement it in correct SQL - it looks like you have PostgreSQL and the field is timestamptz (great choice), so:

9

@jangaraj When I look at the raw data, the timestamps are actually timestamp (or timestamptz) in the JSON. But the issue arises from casting the timestamps (or timestampz I tested both) to integer. The thing is that Grafana converts the timestamps to the standard time format defined for the interface. So you need to trick around to get timestamps back because its the only format for passing time ranges in data links (at least to my knowledge).

10

@yosiasz Thanks for the hint. However using HTML or markdown with the Business Text is not straight forward to me, but I will definitely test it when time allows.

1 Like
11

I don’t understand what is a problem:

obrázok
obrázok2350×1206 242 KB