Time Series by Specific dates in column

Grafana PostgreSQL
, ,
1

So, I would like to achieve a Time Series Panel with the sum of all volumes per area over a selected period.

I am new to this and I am not seeing any data on the Panel:

image
image1502×668 30.7 KB

How do I modify this query to include the timeseries to match report_date?

SELECT SUM(volume) as "Volume", report_date, area from test
group by report_Date, area

Return of Query:
image

I would like to see on the X-Axis 2022-04-18, 2022-04-25 and the measurement volume for area1 and area2 on the y-axis. I need this dynamically so that more data can be uploaded and automatically formatted per report_date.

My table and table data:

[‘id’, ‘report_date’, ‘area’, ‘team’, ‘volume’, ‘sales’]
[‘1’, ‘2022-04-17 19:00:00’, ‘area1’, ‘A’, ‘33’, ‘69’]
[‘2’, ‘2022-04-17 19:00:00’, ‘area1’, ‘A’, ‘3’, ‘45’]
[‘3’, ‘2022-04-17 19:00:00’, ‘area1’, ‘B’, ‘26’, ‘74’]
[‘4’, ‘2022-04-17 19:00:00’, ‘area1’, ‘B’, ‘44’, ‘59’]
[‘5’, ‘2022-04-17 19:00:00’, ‘area1’, ‘B’, ‘90’, ‘94’]
[‘6’, ‘2022-04-17 19:00:00’, ‘area1’, ‘C’, ‘123’, ‘88’]
[‘7’, ‘2022-04-17 19:00:00’, ‘area1’, ‘C’, ‘12’, ‘4’]
[‘8’, ‘2022-04-17 19:00:00’, ‘area2’, ‘A’, ‘134’, ‘77’]
[‘9’, ‘2022-04-17 19:00:00’, ‘area2’, ‘A’, ‘2’, ‘91’]
[‘10’, ‘2022-04-17 19:00:00’, ‘area2’, ‘B’, ‘252’, ‘93’]
[‘11’, ‘2022-04-17 19:00:00’, ‘area2’, ‘C’, ‘67’, ‘22’]
[‘12’, ‘2022-04-17 19:00:00’, ‘area2’, ‘C’, ‘65’, ‘68’]
[‘13’, ‘2022-04-24 19:00:00’, ‘area1’, ‘A’, ‘77’, ‘88’]
[‘14’, ‘2022-04-24 19:00:00’, ‘area1’, ‘A’, ‘33’, ‘26’]
[‘15’, ‘2022-04-24 19:00:00’, ‘area1’, ‘B’, ‘87’, ‘8’]
[‘16’, ‘2022-04-24 19:00:00’, ‘area1’, ‘B’, ‘3’, ‘67’]
[‘17’, ‘2022-04-24 19:00:00’, ‘area1’, ‘C’, ‘8’, ‘89’]
[‘18’, ‘2022-04-24 19:00:00’, ‘area1’, ‘C’, ‘57’, ‘41’]
[‘19’, ‘2022-04-24 19:00:00’, ‘area2’, ‘A’, ‘65’, ‘19’]
[‘20’, ‘2022-04-24 19:00:00’, ‘area2’, ‘A’, ‘41’, ‘12’]
[‘21’, ‘2022-04-24 19:00:00’, ‘area2’, ‘B’, ‘87’, ‘46’]
[‘22’, ‘2022-04-24 19:00:00’, ‘area2’, ‘C’, ‘55’, ‘90’]

table creation

CREATE TABLE test (
id serial primary key
report_date timestamp not null
area varchar not null
team varchar not null
volume int not null
sales int not null)

2

Thanks for providing data. Make sure to include proper schema definitions. could be because you are posting it without the three ticks for code this tick > ` < three times before and after your ddl and dml sample

--DDL
CREATE TABLE test (
id serial primary key
report_date timestamp not null
area varchar not null
team varchar not null
volume int not null
sales int not null)

insert into test 
values(
'1', '2022-04-17 19:00:00', 'area1', 'A', '33', '69'
)

also please post with proper single quotes

-04-17 19:00:00’, ‘area1’, ‘A’, ‘33’, ‘69’]

Since you are asking us to help you, I think it would fair to ask you to test things out before us trying it out on local instances of databases.

3

I am not sure how to add the proper quotes, but here is the script to insert all the values into the test table:

insert into test values (‘1’, ‘2022-04-17 19:00:00’, ‘area1’, ‘A’, ‘33’, ‘69’)
insert into test values (‘2’, ‘2022-04-17 19:00:00’, ‘area1’, ‘A’, ‘3’, ‘45’)
insert into test values (‘3’, ‘2022-04-17 19:00:00’, ‘area1’, ‘B’, ‘26’, ‘74’)
insert into test values (‘4’, ‘2022-04-17 19:00:00’, ‘area1’, ‘B’, ‘44’, ‘59’)
insert into test values (‘5’, ‘2022-04-17 19:00:00’, ‘area1’, ‘B’, ‘90’, ‘94’)
insert into test values (‘6’, ‘2022-04-17 19:00:00’, ‘area1’, ‘C’, ‘123’, ‘88’)
insert into test values (‘7’, ‘2022-04-17 19:00:00’, ‘area1’, ‘C’, ‘12’, ‘4’)
insert into test values (‘8’, ‘2022-04-17 19:00:00’, ‘area2’, ‘A’, ‘134’, ‘77’)
insert into test values (‘9’, ‘2022-04-17 19:00:00’, ‘area2’, ‘A’, ‘2’, ‘91’)
insert into test values (‘10’, ‘2022-04-17 19:00:00’, ‘area2’, ‘B’, ‘252’, ‘93’)
insert into test values (‘11’, ‘2022-04-17 19:00:00’, ‘area2’, ‘C’, ‘67’, ‘22’)
insert into test values (‘12’, ‘2022-04-17 19:00:00’, ‘area2’, ‘C’, ‘65’, ‘68’)
insert into test values (‘13’, ‘2022-04-24 19:00:00’, ‘area1’, ‘A’, ‘77’, ‘88’)
insert into test values (‘14’, ‘2022-04-24 19:00:00’, ‘area1’, ‘A’, ‘33’, ‘26’)
insert into test values (‘15’, ‘2022-04-24 19:00:00’, ‘area1’, ‘B’, ‘87’, ‘8’)
insert into test values (‘16’, ‘2022-04-24 19:00:00’, ‘area1’, ‘B’, ‘3’, ‘67’)
insert into test values (‘17’, ‘2022-04-24 19:00:00’, ‘area1’, ‘C’, ‘8’, ‘89’)
insert into test values (‘18’, ‘2022-04-24 19:00:00’, ‘area1’, ‘C’, ‘57’, ‘41’)
insert into test values (‘19’, ‘2022-04-24 19:00:00’, ‘area2’, ‘A’, ‘65’, ‘19’)
insert into test values (‘20’, ‘2022-04-24 19:00:00’, ‘area2’, ‘A’, ‘41’, ‘12’)
insert into test values (‘21’, ‘2022-04-24 19:00:00’, ‘area2’, ‘B’, ‘87’, ‘46’)
insert into test values (‘22’, ‘2022-04-24 19:00:00’, ‘area2’, ‘C’, ‘55’, ‘90’)

4

what does it say when you hover over that red exclamation top left?