英文:
Postgresql problems
问题
我需要执行以下查询以返回所需结果：
SELECT
sum(used) as total,
time as tm
FROM table
GROUP BY tm
请注意，您的原始查询中使用了DISTINCT(time)，但这会导致您获得重复的时间值。在上面的查询中，我删除了DISTINCT，并将time用作tm列的别名，以获得您所需的结果。
英文:
i have a table like this:
name used time
asd 10 15:00
bsf 15 15:00
asd 20 14:55
bsf 0 14:55
i need to make a query that returns values like this:
the result i need for the grafana timeseries is:
total tm
25 15:00
20 14:55
i've tried using:
SELECT
DISTINCT(time) as tm,
sum(used) as total
FROM table
GROUP BY tm
but that doesnt works everything i try gives me the repeated time values
答案1
得分: 0
回顾PostgreSQL文档是开始使用时间戳的好方法。以下是按HH:MI:SS分组和聚合的一种方法：
with my_table as (
select current_timestamp as time_column, 20 as used union all
select current_timestamp, 5 union all
select current_timestamp - INTERVAL '10 Seconds', 15
)
select to_char(time_column,'HH24:MI:SS') as time_col, sum(used) as used
from my_table
group by 1
order by 1;
| time_col | used |
|---|---|
| 19:43:35 | 15 |
| 19:43:45 | 25 |
基本上，这种类型的转换是您的朋友：
to_char(time_column,'HH24:MI:SS')
英文:
Reviewing the postgres docs is a great place to start when working with timestamps. Here's a way to group by HH:MI:SS and aggregate:
with my_table as (
select current_timestamp as time_column, 20 as used union all
select current_timestamp, 5 union all
select current_timestamp - INTERVAL '10 Seconds', 15
)
select to_char(time_column,'HH24:MI:SS') as time_col, sum(used) as used
from my_table
group by 1
order by 1;
| time_col | used |
|---|---|
| 19:43:35 | 15 |
| 19:43:45 | 25 |
Basically, this type of casting is your friend:
to_char(time_column,'HH24:MI:SS')
答案2
得分: 0
我使用date_trunc()函数成功解决了这个问题，以选择时间值，因为列的类型是timestamptz，所以查询最终如下所示：
select
date_trunc('minute', time) as tm,
sum(used) as total
from
table
group by
tm
英文:
I managed to solve the problem by using the date_trunc() function to select the time value since the column was of type timestamptz the query ended up looking like this
select
date_trunc('minute', time) as tm,
sum(used) as total
from
table
group by
tm
答案3
得分: 0
以下是翻译好的部分：
创建表Table1
("name" varchar(3), "used" int, "time" varchar(5))
;
向Table1插入数据
("name", "used", "time")
VALUES
('asd', 10, '15:00'),
('bsf', 15, '15:00'),
('asd', 20, '14:55'),
('bsf', 0, '14:55')
;
选择
SUM("used"), "time"
从Table1
按"time"分组
英文:
You are looking for a simple GROUP BY
CREATE TABLE Table1
("name" varchar(3), "used" int, "time" varchar(5))
;
INSERT INTO Table1
("name", "used", "time")
VALUES
('asd', 10, '15:00'),
('bsf', 15, '15:00'),
('asd', 20, '14:55'),
('bsf', 0, '14:55')
;
SELECT
SUM("used"), "time"
FROM Table1
GROUP BY "time"
| sum | time |
|---|---|
| 25 | 15:00 |
| 20 | 14:55 |
| > ``` status | |
| > SELECT 2 | |
| > ``` |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论