Grafana is adding time to my date column

Hello everyone,

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

    • Grafana 8.3.4

    • Debian 11

  • What are you trying to achieve?

    Create a table with a postgresql query

  • What happened?

i’m using this query to show some data from a table.

select dop.operation_date,
	dpw.platform_name as plateform,
	df.fiat_name as fiat,
	dop.amount_spent
from table1 dop
inner join table2 df on df.id = dop.fiat_id
inner join table3 dpw on dpw.id = dop.plateform_id
order by dop.operation_date desc, dop.creation_date desc
limit 10

The first column operation date is showing this way on grafana :

image

But the column in my table in Postgresql is in DATE format without any hour, minute, second. I don’t know from where grafana could find those values for the hour.

I checked with that to make sur that my data in my database was in good format

SELECT date_part('hour',operation_date)
from my_table;

and everything is ok. I got a nice 0 for all the lines. Not any 1 or 2 as it is showed in grafana.

  • What did you expect to happen?

My data on grafana should be the same as what they are in my BDD, like this :

image

Thanks a lot for your help to a fresh newbie on grafana.

Welcome to the Grafana forum.

If I had to guess what is happening, I’d say the timestamp date you store in Postgresql database is without a time zone, and when Grafana reads the data, it is adding the local GMT +2 (or +1 depending on the month of the year…probably has to do with daylight savings).

To get rid of the (seemingly useless) hh:mm:ss, use an override, like this:

BEFORE:

AFTER:

Thanks for your answer, it’s working !

But i don’t understand why Grafana doesn’t take the data as the same format as it is in the DB. The column type in my database is DATE. i’m not storing any timestamp, just a date.

1 Like

same issue I am facing

Hi, does anyone have a solution for that problem?

I change the ‘timestamp’ to ‘DATE_SUB(TIMESTAMP, INTERVAL 1 HOUR) as TIMESTAMP’ at my sql-query. It does the trick but kinda unprofessional, isnt it?

I got the same issue, here is my solution. As @grant2 said, you need to use overrides to do this.
Step1: add field override, select “Fields with type”, since the column you want to edit should be in time, so you should select “Time(1)” from drop down list.
Step2: click “add override property”, select “Standard options > Unit”, then you need to type in rather than select from dropdown list with following time: YYYY-MM-DD, so your timestamp will now become something like 2023-12-25. Type in rather than select the dropdown menu will allow you to customise the format.
Hope it helps.
BEFORE:


AFTER:

2 Likes