Discrepancy when pulling column of 'date' type from PostgreSQL

Grafana Table Panel
,
1

  • Grafana v11.2.2 (c47b921ef4)

  • I need to pull several rows from Postgres db. One of the columns is of ‘date’ type.

  • I made the query short in attempt to troubleshoot.
    SELECT DISTINCT batch_dt FROM nodes ORDER BY 1

  • All dates in DB are shifted one day back.

    image
    image546×611 28.4 KB

Every component is in CST zone: Postrges, Grafana, personal computer, browser.

[view@chlc-agas01 set03]$ timedatectl
      Local time: Tue 2024-11-05 21:18:07 CST
  Universal time: Wed 2024-11-06 03:18:07 UTC
        RTC time: n/a
       Time zone: US/Central (CST, -0600)
     NTP enabled: no
NTP synchronized: yes
 RTC in local TZ: no
      DST active: no
 Last DST change: DST ended at
                  Sun 2024-11-03 01:59:59 CDT
                  Sun 2024-11-03 01:00:00 CST
 Next DST change: DST begins (the clock jumps one hour forward) at
                  Sun 2025-03-09 01:59:59 CST
                  Sun 2025-03-09 03:00:00 CDT

image
image355×193 10.3 KB

  • What did you expect to happen?
    I would expect to see in Grafana what I see in DB console.
    I am also confused with the fact that the column looks like a ‘timestamp’ type. There should not be a time component!
PSQL:avg_02/view@localhost=> SELECT DISTINCT batch_dt FROM nodes ORDER BY 1;
  batch_dt
------------
 2024-10-28
 2024-10-29
 2024-10-30
 2024-10-31
 2024-11-01
 2024-11-04
 2024-11-05
(7 rows)

PSQL:avg_02/view@localhost=> SELECT CURRENT_SETTING('timezone');
 current_setting
-----------------
 US/Central
(1 row)

PSQL:avg_02/view@localhost=> SHOW timezone;
  TimeZone
------------
 US/Central
(1 row)

PSQL:avg_02/view@localhost=> \d nodes
                 Table "public.nodes"
   Column   |            Type             | Modifiers
------------+-----------------------------+-----------
 env        | character varying(5)        |
 batch_dt   | date                        |
2
3

Hello jangaraj.
Thank you for sharing a link to the post.
I see your point. Timestamps are stored in UTC.
However,

DATE type: Stores only the date (year, month, day) without a time or timezone component. It is stored as a 4-byte integer representing the number of days relative to the PostgreSQL epoch date (2000-01-01). The DATE type does not involve timezone conversions, so it’s not stored as epoch time in UTC.
Only TIMESTAMP WITH TIME ZONE values are stored in UTC.

Grafana grabs a date value, takes liberty to treat it as a “timestamp with time zone” (actually using UTC), subtracts time difference and throws it as an output.
For example. ‘2024-11-05’ value is stored in DB, Grafana assumes it is “2024-11-05 00:00:00-00” (not sure about the trailing part), subtracts 6 hours and gives me “2024-11-04 18:00:00”.
Nice! That also explains why it is 19 hours vs 18 hours for days before November 3rd - we switched from CDT to CST, correspondingly UTC-5 and UTC-6.
I need to adjust my queries to make Postgres return date/time values in the appropriate (string) format.

Do you not think Grafana assumes too much? I would consider it a bug. Not a top priority but it is.
Regards.

1 Like
4

Mhm, I think that’s the best option what Grafan can do. It is designated for timeseries with milisecond precison and you have date with day precision.

I would set UTC dashboard timezone in your case, so Grafana won’t perform any local timezone shifts.

5

I would set UTC dashboard timezone in your case, so Grafana won’t perform any local timezone shifts.

That has no effect.
Just to close this topic. I will delegate conversion to string (to_char function) to Postgres. Makes queries a bit fancy but the result is exactly what I would expect.

6

Then I guess your Grafana sql user doesn’t have UTC timezone. You may try to reset session timezone with

SET TIME ZONE 'UTC';

Casting to varchar helps for tables. But I guess it will be a problem for panels where you need timeseries.

1 Like
7

Jan,
Thank you for the suggestion. Indeed, that partially solves the problem - date part is as expected. Trailing time part remains. This is not critical at this point - I will stick to my approach. My dashboard is not about timeseries. It is a Node Graph based on volkovlabs plugin.