The date & time of datetime/timestamp columns not being read correctly

Grafana Time Series Panel
1

  • What Grafana version and what operating system are you using?
    9.1.3

  • What are you trying to achieve?
    Trying to plot a time series chart from a table in the Vertica database

  • How are you trying to achieve it?
    by including the time series column (timestamp_tz) and a metric column (CPU utilization)

  • What happened?
    the timestamp of the time series column is transformed to a constant time of “31-Aug-1754 04:37:09.129”, irrespective of what the date-time in the time series column in the datasource.

  • What did you expect to happen?
    Show the same timestamp as in the datasource

  • Can you copy/paste the configuration(s) that you are having problems with?
    Here is a snapshot of the data from my database using the query “SELECT timestamp_tz, cpu_util_pct_avg FROM opsb_sysinfra_node_1h ORDER BY cpu_util_pct_avg”:
    image

And see the snapshot from Grafana explore using the query: “SELECT timestamp_tz, cpu_util_pct_avg FROM opsb_sysinfra_node_1h ORDER BY cpu_util_pct_avg”

image
image1828×350 23.4 KB

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    No related errors in the log file

  • Did you follow any online instructions? If so, what is the URL?

2

Hello
Look like it’s working , but your date is 250 years ago…
can you convert this to unix timestamp ? are your in time series mode in the panel ?
i think you have to rename the time fields with an alias named time too,
I believe it’s close to mysql usage.
may you can check this link :

3

Thanks @alexandrearmand. My date in the database is current, see the output of the query from my database in the first screenshot I posted.

In the meantime I resolved the issue by using the Vertica function to_timestamp_tz(<epoch in seconds>). Interestingly, the Vertica function to_timestamp(<epoch in seconds>) too doesn’t work and gives the same date from 1784.

But this is a workaround, and I would still like to understand why I am not able to directly use a datetime field as is.

Thanks
-Ankit

4

Hi, just as a follow up on the root cause. We investigated this and found the following:
If the database is running with TZ set to any timezone that is UTC+ or - a whole hour (e.g. UTC+5 or UTC-3:00) it works fine out of the box.
If the database is running with a TZ set to any timezone that is UTC+ or - a partial hour (e.g. Asia/Kolkata is UTC+5:30) then it gives the odd 1754 year.
We found if we set the dashboard timezone to the same as the TZ on the Vertica end, and in the query we used end_time as timezone “TZ as set for Vertica and dash”, then the time is correct in Grafana but it’s adjusted to the actual TZ time vs your browser time.
We are investigating further to try to resolve in a future release of the plugin.

1 Like
5

@scrossman FYI, I solved by using the function to_timestamp_tz, which works fine.

In the meantime, has this been addressed in a subsequent release of the plugin?

Thanks
-Ankit

6

Ankit,

Not resolved yet. We had done some further debugging to verify this wasn’t a Vertica sql-go driver issue. by creating a standalone go program to fetch the data. It came back as expected. We also did some Grafana end testing and feel confident it is in the plugin. Then we reviewed the macros and code in the src but found no obvious reason for the issue. We’ve added the issue to our list of potential fixes/enhancements for a future release. The code developers can then dig into it at code level and confirm if it’s a plugin bug and if so what the potential fix is.