英文:
How to get a list of hours between two timestamp?
问题
我想要获取两个时间戳之间的所有小时。
例如，从2023-02-23 14:38到2023-02-23 19:32
结果应该是
141516171819
最好我还能在计算的小时中获取分钟
| 小时 | 分钟 |
|---|---|
| 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
141516171819
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
获取小时的简单解决方案：
SELECTextract(hour FROM a) AS hourFROMgenerate_series(date_trunc('hour', '2023-02-23 14:38'::timestamp),date_trunc('hour', '2023-02-23 19:32'::timestamp), '1 hour')AS t (a);
更新后包括分钟：
SELECTextract(hour FROM a) AS hour,CASE WHEN a = '2023-02-23 14:00'::timestamp THENextract(minute from '2023-02-23 14:38'::timestamp - a)WHEN a = '2023-02-23 19:00'::timestamp THENextract(minute from '2023-02-23 19:32'::timestamp - a)ELSEto_char(a - lag(a) over(), 'HH')::integer * 60END AS minutesFROMgenerate_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:
SELECTextract(hour FROM a) AS hourFROMgenerate_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------141516171819
UPDATED to include minutes.
SELECTextract(hour FROM a) AS hour,CASE WHEN a = '2023-02-23 14:00'::timestamp THENextract(minute from'2023-02-23 14:38'::timestamp - a)WHEN a = '2023-02-23 19:00'::timestamp THENextract(minute from '2023-02-23 19:32'::timestamp - a)ELSEto_char(a - lag(a) over(), 'HH')::integer * 60END AS minutesFROMgenerate_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 | 3815 | 6016 | 6017 | 6018 | 6019 | 32
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论