Show week number and date in X axis (grafana on postgres datasource)

Grafana PostgreSQL
, ,
1

Hi! I’ve connected to a postgres datasource with Grafana, and I wanted to build a time series out of a table using a line plot, not a bar plot.

The table consists of essentially three columns: 1) date_created (timestamp), 2) week (integer) which corresponds positively to date_created, and 3) quantity. My goal is to plot quantitiy in the Y axis with respect to week number as X axis (e.g., instead of 2024-02, 2024-03, 2024-04, …, I have 12, 13, 14, …), but also attach the date created. The query itself is rather simple:

select date_created, quantity from table_data

I’ve seen similar posts but no solution yet…

Any advice on how to proceed?

Note on relation between week and date_created: week 0 is some hardcoded date e.g. 1/1/20. Then week 1 is 1/8/20, week 2 is 1/5/20, etc…

After some thought, I think this can be reduced to a formatting question. If I can format the X axis like “Week {week}, {date_created}”, that would also work, or “{date_created}, {date_to_week(date_created)}”.

2

Please follow the steps:
Step 1 Create a postges insert dummy Data and

Create the table

CREATE TABLE table_data (
    id SERIAL PRIMARY KEY,
    date_created DATE NOT NULL,
    quantity INTEGER NOT NULL
);

Insert sample data

INSERT INTO table_data (date_created, quantity) VALUES
('2025-03-18', 200),  
('2025-03-25', 150),  
('2025-04-01', 175),  
('2025-04-08', 190),  
('2025-04-15', 210),  
('2025-04-22', 220);

Step 2 Integrate postgres with grafana

Step 3 Write query in grafana for group as week

SELECT
  CONCAT('Week ', EXTRACT(WEEK FROM date_created), ', ', TO_CHAR(date_created, 'YYYY-MM-DD')) AS label,
  SUM(quantity) AS quantity
FROM table_data
GROUP BY 1
ORDER BY 1;

Step 4 Select Bar Chart as Visualization

Final Output :-

image
image1047×405 18.6 KB

1 Like
3

Hello @davidjli9731
Just checking in—did this solution resolve your issue, or are you still experiencing the same problem?