Postgresql中的日期间隔未显示正确的结果
Postgresql中的日期间隔未显示正确的结果
提问于 2017-04-18 03:35:46
运行此查询时：
SELECT start_date,
end_date,
extract(epoch from end_date::timestamp - start_date::timestamp)/(24*60*60) as total,
extract(epoch from end_date::timestamp - GETDATE()::timestamp)/(24*60*60) as left
FROM app_data.content_cards我得到一张这样的桌子：
+---------------------+---------------------+-------+-------+
| start_date | end_date | total | left |
+---------------------+---------------------+-------+-------+
| 2017-03-30 22:00:00 | 2017-04-07 22:00:00 | 8 | -9.89 |
+---------------------+---------------------+-------+-------+
| 2017-04-09 22:00:00 | 2017-04-11 22:00:00 | 2 | -5.89 |
+---------------------+---------------------+-------+-------+
| 2017-04-03 22:00:00 | 2017-04-11 22:00:00 | 8 | -5.89 |
+---------------------+---------------------+-------+-------+
| 2017-03-30 22:00:00 | 2017-04-18 22:00:00 | 19 | 1.11 |
+---------------------+---------------------+-------+-------+
| 2017-04-09 22:00:00 | 2017-04-15 22:00:00 | 6 | -1.89 |
+---------------------+---------------------+-------+-------+
| 2017-04-02 22:00:00 | 2017-05-20 22:00:00 | 78 | 63.11 |
+---------------------+---------------------+-------+-------+
| 2017-04-23 22:00:00 | 2017-04-29 22:00:00 | 6 | 12.11 |
+---------------------+---------------------+-------+-------+
| 2017-04-02 22:00:00 | 2017-05-20 22:00:00 | 78 | 63.11 |
+---------------------+---------------------+-------+-------+但是如果我使用这个查询：
SELECT start_date,
end_date,
end_date - start_date as total,
end_date - GETDATE() as days
FROM app_data.content_cards我得到一张这样的桌子：
+--------------------+-------------------+-------+-----------------+
| start_date | end_date | total | left |
+--------------------+-------------------+-------+-----------------+
|2017-03-30 22:00:00 |2017-04-07 22:00:00|8 days |-9 days -21:21:47|
+--------------------+-------------------+-------+-----------------+
|2017-04-09 22:00:00 |2017-04-11 22:00:00|2 days |-5 days -21:21:47|
+--------------------+-------------------+-------+-----------------+
| 2017-04-03 22:00:00|2017-04-11 22:00:00|8 days |-5 days -21:21:47|
+--------------------+-------------------+-------+-----------------+
| 2017-03-30 22:00:00|2017-04-18 22:00:00|19 days|1 day 02:38:13 |
+--------------------+-------------------+-------+-----------------+
| 2017-04-09 22:00:00|2017-04-15 22:00:00|6 days |-1 days -21:21:47|
+--------------------+-------------------+-------+-----------------+
| 2017-04-02 22:00:00|2017-05-20 22:00:00|48 days|33 days 02:38:13 |
+--------------------+-------------------+-------+-----------------+
| 2017-04-23 22:00:00|2017-04-29 22:00:00|6 days |12 days 02:38:13 |
+--------------------+-------------------+-------+-----------------+
| 2017-04-02 22:00:00|2017-05-20 22:00:00|48 days|33 days 02:38:13 |
+--------------------+-------------------+-------+-----------------+请注意从底部开始的第1行和第3行中的不同结果。正确的应该是最后一个查询返回的那个。
我需要的是在几天内和两个小数的结果(就像在第一个表中)，但数据是正确的(像第二个)。另外，我需要数据在结果中没有‘天’，只需要两个小数点的值。
我如何才能做到这一点？
我使用的是: i686-pc-linux-gnu上的PostgreSQL 8.0.2版本，由GCC、gcc (GCC) 3.4.2 20041017 (RedHat3.4.2-6.fc3)编译，Redshift 1.0.1232
回答 1
Stack Overflow用户
发布于 2017-04-18 03:59:30
此查询应该适用于您。
select start_date,
end_date,
(date_part(epoch,end_date)::float- date_part(epoch,start_date)::float)/(24*60*60) as total,
(date_part(epoch,end_date)::float- date_part(epoch,sysdate)::float)/(24*60*60) as total left
from app_data.content_cards或
你也可以使用你的提取函数。
SELECT start_date,
end_date,
(extract(epoch from end_date)::float - extract(epoch from start_date)::float)/(24*60*60) as total,
(extract(epoch from end_date)::float - extract(epoch from sysdate)::float)/(24*60*60) as left
FROM app_data.content_cards如果我做了一个糟糕的假设，请发表评论，我将重新关注我的答案。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43458330
复制相关文章
相似问题
腾讯云开发者
