Timestamp in postgresql database is not UTC time - problems with Grafana queries

[terry-sydaus]

I have noted that the time stamp in the withdrawals table/panel of the Grafana validator stats dashboard was incorrect by an amount equal to my time zone. This problem was also observed in other panels of the Grafana validator stats dashboard (eg. Wallet Balance History panel, all 3 of the Validator Rewards bar chart panels).

I fixed the Wallet Balance History by changing the query from

SELECT 
"timeStamp", 
"index",
"blockNumber", 
"validatorIndex",
amount * $pls_precision as PLS, 
amount * $pls_precision * $pls_price as USD,
"priceUSD" as "$ at W"

FROM pls_validator_withdrawals WHERE address = '$pls_wallet'
order by 2 desc

to

SELECT 
"timeStamp" - interval 'x hour' as timeStamp, 
"index",
"blockNumber", 
"validatorIndex",
amount * $pls_precision as PLS, 
amount * $pls_precision * $pls_price as USD,
"priceUSD" as "$ at W"

FROM pls_validator_withdrawals WHERE address = '$pls_wallet'
order by 2 desc

where x equals the number of hours added by Grafana to the time stamp recorded in the postgresql database by the pls_admin app.

I implemented similar changes to the underlying queries of other affected Grafana panels that reference either a date or timestamp field from the postgresql database source.

The above query modifications are, IMHO, a hack and clumsy. It seems to me that it would be better to have the pls_admin app write UTC timestamps to the database.

[raskitoma]

Hi, sorry for not fixing this yet. Instead of using a time modifier like - interval 'x hour', since I'm assuming we're using PostgreSQL(I don't know if MySQL have this feature) to use the at time zone modifier, like this:

select some_date at time zone 'Europe/Berlin' from some_table

I know this will be easy to implement, but I prefer to do it using a variable, that is, setting at install time a "timezone" var using the std timezone table to allow the query to select the times correctly and related to your time zone.

Gimme a couple of days to fix this and include it in the package as well.

[raskitoma]

Also, I think I can use the std autodetected timezone by Grafana. If I can recall correctly there's a variable for that.

[raskitoma]

Solved adding variable to control timezone on selected panels. Commit e3ddcf8

Have not found a proper variable from the ones that Grafana uses (this would be ideal). Right now you must have to define it in the variable configuration at install or also in the settings for the dashboard.