postgresql 类型间隔的输入语法无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28283807/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Invalid input syntax for type interval
提问by Jacob Lambert
I am encountering an issue where I am not able to add intervals to a date in postgresql. In MySql I would do this:
我遇到了无法在 postgresql 中为日期添加间隔的问题。在 MySql 中，我会这样做：
date_add('2015-02-02', interval -46 day)
with the corresponding statement in postgresql being:
与 postgresql 中的相应语句是：
'2015-02-02' + -46 * interval '1 day'
But, when I do so I get an error of:
但是，当我这样做时，我得到一个错误：
[Err] ERROR: invalid input syntax for type interval: "2015-02-02"
If I remove the + -46 * interval '1 day'section of the statement, it works as expected.
如果我删除+ -46 * interval '1 day'语句的部分，它会按预期工作。
The simplified query:
简化查询：
SELECT
cd.date_sort_prequeue_start AS date,
SUM(CASE WHEN cd.call_conclusion='Answered' THEN 1 ELSE 0 END) AS calls_answered
FROM
data_warehouse.call_detail AS cd
INNER JOIN data_warehouse.users_history AS uh ON cd.users_history_id=uh.id
WHERE
cd.date_sort_prequeue_start>= '2015-02-02' + (-46 * INTERVAL '1 day')
AND cd.date_sort_prequeue_start<'2015-02-02'
AND cd.call_type='I' AND uh.team='TeamOne'
GROUP BY
cd.date_sort_prequeue_start
ORDER BY
cd.date_sort_prequeue_start;
回答by Politank-Z
If you cast your string as a date, that will resolve the problem:
如果您将字符串转换为日期，则可以解决问题：
'2015-02-02'::date + -46 * interval '1 day'
The string on its own is ambiguous as there are several different types to represent time.
字符串本身是不明确的，因为有几种不同的类型来表示时间。
回答by Tatyana
An easier way to do it would be:
一个更简单的方法是：
date '2015-02-02' - interval '46 days'
