是否有可能根据同一栏目中出现的时间差异来计算每天的总时数?
date | time |
---|---|
2017-11-08 | 08:25:57 |
2017-11-08 | 12:31:59 |
2017-11-08 | 13:28:42 |
2017-11-08 | 17:34:13 |
2017-11-09 | 08:28:08 |
2017-11-09 | 12:31:15 |
2017-11-09 | 13:20:13 |
使用下面的查询,我可以获得enditime和Startime的单独数据:
SELECT MAX(time) AS "endtime",
MIN(time) AS "startime",
MAX(time)-MIN(time) as "totalneedtocalculatepause"
WHERE (date = '2017-11-08'::date )
FROM history ts
但是缺少startpsuse和endpsuse:
endtime | startime | totalneedtocalculatepause |
---|---|---|
17:34:13 | 08:25:57 | 09:08:16 |
正确的顺序应该是:
day startpause starttime endtime endpause
2017-11-08: (12:31:59 - 08:25:57) + (17:34:13 - 13:28:42)
我使用的是PostgreSQL13.