Weird date manipulation with SQL results

[msgre]

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

v9.1.5 (df015a9301)

• What are you trying to achieve?

I want to see same results in Grafana as I can see in psql console.

• How are you trying to achieve it?

Using same query in psql (1) and then Grafana (2).

• What happened?

Outputs from (1) and (2) are not identical.

• What did you expect to happen?

Same results.

• Can you copy/paste the configuration(s) that you are having problems with?

Query I am using:

SELECT
  last_updated::date AS "time",
  state::float
FROM states
WHERE
  state not in ('unknown', 'unavailable', '') and entity_id = 'sensor.estimated_energy_production_sum_today';

In psql console I can see values like:

2022-11-03	14.52
2022-11-03	17.66
2022-11-04	8.68
2022-11-04	0.37
2022-11-04	0.38
2022-11-04	0.39

When I paste same query to Grafana and display it as table, I can see this:

2022-11-03 01:00:00  11.2
2022-11-03 01:00:00  14.5
2022-11-03 01:00:00  17.7
2022-11-03 01:00:00   8.68
2022-11-03 01:00:00   0.370
2022-11-04 01:00:00   0.380
2022-11-04 01:00:00   0.390

Problems:

1. Grafana interpret date as datetime (I can live with this)
2. Check value 0.370 – in psql there is date 2022-11-04, but in Grafana 2022-11-03 01:00:00. This is my problem (value 0.370 belong to 2022-11-04, not 2022-11-03)

Original data in my DB are stored with timezone:

2022-11-03 18:14:20.453231+01   11.21
2022-11-03 22:14:23.873654+01   14.52
2022-11-03 22:14:23.940703+01   17.66
2022-11-04 00:14:23.44796+01    8.68
2022-11-04 00:14:23.474613+01   0.37
2022-11-04 02:14:23.497958+01   0.38
2022-11-04 02:14:23.500527+01   0.39
2022-11-04 04:14:23.294223+01   0.38

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

No.

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

No.

[yosiasz]

Welcome

what time zone is that datetime field?

[msgre]

last_updated column is defined as timestamp with time zone, data are stored with Europe/Prague timezone, like:

2022-11-04 00:14:23.474613+01   0.37

Of course, DST time changed here in Europe recently, so records from September are stored like:

2022-09-01 00:14:23.474613+02

In Grafana settings (/?orgId=1&editview=settings) I have Time zone set to Browser time, which should be again Europe/Prague. I checked also Data Source settings in Grafana for my PostgreSQL DB, but I didn’t find there anything related to timezone.

[msgre]

Hi! No thoughts? Is it Grafana bug or I am doing something wrong?

Thanks for any hint.

[jangaraj]

That’s not a Grafana bug. You are casting timestamp with timezone to date (so detail about timezone is lost). Grafana assumes that date is UTC + it “moves” it to the dashboard time zone

Anyeay, I don’t see a reason why you need to cast timestamp with tinezone to date.

[msgre]

Thanks for the response.

I sat down again and found the reason why I see slightly different dates in Grafana and the psql console.

I had forgotten that I had explicitly set a custom time zone in psql, ie.:

SET timezone='Europe/Prague';

When I then did a query over the states table, I saw the datetime data shifted to my timezone:

SELECT
  last_updated AS "time",
  state::float
FROM
  states
WHERE
  state not in ('unknown', 'unavailable', '') AND
  entity_id = 'sensor.estimated_energy_production_sum_today';

                          time | state
-------------------------------+-------
 2022-11-03 00:14:19.189275+01 | 11.28
 2022-11-03 00:14:19.351068+01 | 15.78
 2022-11-03 02:14:20.764514+01 | 16.35
 2022-11-03 02:14:20.784358+01 | 16.6
 2022-11-03 04:14:20.304387+01 | 16.94
 2022-11-03 04:14:20.415235+01 | 17.58
 ...

But if I execute the query right after launching psql, timezone remains in default state Etc/UTC, then I get the data in UTC:

                          time | state
-------------------------------+-------
 2022-11-03 01:14:20.764514+00 | 16.35
 2022-11-03 01:14:20.784358+00 | 16.6
 2022-11-03 03:14:20.304387+00 | 16.94
 2022-11-03 03:14:20.415235+00 | 17.58
 2022-11-03 05:14:20.329912+00 | 18.22
 2022-11-03 05:14:20.454988+00 | 18.78
 2022-11-03 06:14:20.736681+00 | 18.78

Stupid mistake on my part. Because of this, I still didn’t understand why I see slightly different results in psql and Grafana.

Apropo, Grafana does something similar. If I explicitly specify a timezone in the date widget, it “recalculates” the date fields accordingly, similar to SET timezone="value" in psql.

Anyway, I don’t see a reason why you need to cast timestamp with tinezone to date.

I need to get the min/max value of state for each day in the specified range.

I finally got around to this query:

SELECT
  local_date AS time,
  min(state) AS min,
  max(state) AS max
FROM (
  SELECT
    CAST(last_updated AT TIME ZONE 'Europe/Prague' AS date) AS "local_date",
    state::float
  FROM
    states
  WHERE
    $__timeFilter(last_updated) AND 
    state not in ('unknown', 'unavailable', '') AND 
    entity_id = 'sensor.estimated_energy_production_sum_today'
) q 
GROUP BY
  local_date;

The expression CAST(last_updated AT TIME ZONE 'Europe/Prague' AS date) AS "local_date" converts the datetime from UTC to Europe/Prague timezone, removing the time and leaving only the date. The local_date is then used to do the aggregation and find the min/max. The resulting graph then looks like this:

Thanks again for your reaction.