英文:
How to get a list of hours between two timestamp?
问题
我想要获取两个时间戳之间的所有小时。
例如，从2023-02-23 14:38到2023-02-23 19:32
结果应该是
14
15
16
17
18
19
最好我还能在计算的小时中获取分钟
| 小时 | 分钟 |
|---|---|
| 14 | 38 |
| 15 | 60 |
| 16 | 60 |
| 17 | 60 |
| 18 | 60 |
| 19 | 32 |
在PostgreSQL中是否有此查询的函数？
英文:
I want to get all hours between two timestamps.
For example from 2023-02-23 14:38 to 2023-02-23 19:32
The result should be
14
15
16
17
18
19
better i get also additional the minutes in the calculated hour
| Hour | Minutes |
|---|---|
| 14 | 38 |
| 15 | 60 |
| 16 | 60 |
| 17 | 60 |
| 18 | 60 |
| 19 | 32 |
Is there a function in postgresql for this query ?
答案1
得分: 1
获取小时的简单解决方案：
SELECT
extract(hour FROM a) AS hour
FROM
generate_series(date_trunc('hour', '2023-02-23 14:38'::timestamp),
date_trunc('hour', '2023-02-23 19:32'::timestamp), '1 hour')
AS t (a);
更新后包括分钟：
SELECT
extract(hour FROM a) AS hour,
CASE WHEN a = '2023-02-23 14:00'::timestamp THEN
extract(minute from '2023-02-23 14:38'::timestamp - a)
WHEN a = '2023-02-23 19:00'::timestamp THEN
extract(minute from '2023-02-23 19:32'::timestamp - a)
ELSE
to_char(a - lag(a) over(), 'HH')::integer * 60
END AS minutes
FROM
generate_series(date_trunc('hour', '2023-02-23 14:38'::timestamp),
date_trunc('hour', '2023-02-23 19:32'::timestamp), '1 hour') AS t (a);
英文:
The simple solution to get the hours:
SELECT
extract(hour FROM a) AS hour
FROM
generate_series(date_trunc('hour', '2023-02-23 14:38'::timestamp),
date_trunc('hour', '2023-02-23 19:32'::timestamp), '1 hour')
AS t (a);
hour
------
14
15
16
17
18
19
UPDATED to include minutes.
SELECT
extract(hour FROM a) AS hour,
CASE WHEN a = '2023-02-23 14:00'::timestamp THEN
extract(minute from'2023-02-23 14:38'::timestamp - a)
WHEN a = '2023-02-23 19:00'::timestamp THEN
extract(minute from '2023-02-23 19:32'::timestamp - a)
ELSE
to_char(a - lag(a) over(), 'HH')::integer * 60
END AS minutes
FROM
generate_series(date_trunc('hour', '2023-02-23 14:38'::timestamp),
date_trunc('hour', '2023-02-23 19:32'::timestamp), '1 hour') AS t (a);
hour | minutes
------+---------
14 | 38
15 | 60
16 | 60
17 | 60
18 | 60
19 | 32
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论