SQL 从时间戳日期中减去 1 天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46079791/
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
Subtracting 1 day from a timestamp date
提问by J-Ko
I am using Datagrip for Postgresql. I have a table with a date field in timestamp format (ex: 2016-11-01 00:00:00). I want to be able to:
我正在为 Postgresql 使用 Datagrip。我有一个带有时间戳格式的日期字段的表(ex: 2016-11-01 00:00:00)。我希望能够：
- apply a mathematical operator to subtract 1 day
- filter it based on a time window of today-130 days
- display it without the hh/mm/ss part of the stamp (2016-10-31)
- 应用数学运算符减去 1 天
- 根据今天 - 130 天的时间窗口过滤它
- 显示它没有邮票的 hh/mm/ss 部分 (2016-10-31)
Current starting query:
当前开始查询：
select org_id, count(accounts) as count, ((date_at) - 1) as dateat
from sourcetable
where date_at <= now() - 130
group by org_id, dateat
The ((date_at)-1)clause on line 1 results in:
((date_at)-1)第 1 行的子句导致：
[42883] ERROR: operator does not exist: timestamp without time zone - integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 69
[42883] 错误：运算符不存在：没有时区的时间戳 - 整数 提示：没有运算符匹配给定的名称和参数类型。您可能需要添加显式类型转换。职位：69
The now()clause spawns a similar message:
该now()子句产生了类似的消息：
[42883] ERROR: operator does not exist: timestamp with time zone - integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: ...
[42883] 错误：运算符不存在：带时区的时间戳 - 整数 提示：没有运算符匹配给定的名称和参数类型。您可能需要添加显式类型转换。位置： ...
Online guides to type casts are singularly unhelpful. Input is appreciated.
类型转换的在线指南非常无用。输入表示赞赏。
回答by Michel Milezzi
Use the INTERVALtype to it. E.g:
使用INTERVAL它的类型。例如：
--yesterday
SELECT NOW() - INTERVAL '1 DAY';
--Unrelated to the question, but PostgreSQL also supports some shortcuts:
SELECT 'yesterday'::TIMESTAMP, 'tomorrow'::TIMESTAMP, 'allballs'::TIME;
Then you can do the following on your query:
然后，您可以对查询执行以下操作：
SELECT
org_id,
count(accounts) AS COUNT,
((date_at) - INTERVAL '1 DAY') AS dateat
FROM
sourcetable
WHERE
date_at <= now() - INTERVAL '130 DAYS'
GROUP BY
org_id,
dateat;
TIPS
提示
Tip 1
提示 1
You can append multiple operands. E.g.: how to get last day of current month?
您可以附加多个操作数。例如：如何获得当月的最后一天？
SELECT date_trunc('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH - 1 DAY';
Tip 2
提示 2
You can also create an interval using make_intervalfunction, useful when you need to create it at runtime (not using literals):
您还可以使用make_interval函数创建间隔，当您需要在运行时创建它时很有用（不使用文字）：
SELECT make_interval(days => 10 + 2);
SELECT make_interval(days => 1, hours => 2);
SELECT make_interval(0, 1, 0, 5, 0, 0, 0.0);
More info:
更多信息：
Date/Time Functions and Operators
