SQL:从时间戳日期减去1天

我在Postgresql中使用Datagrip。我有一个带有时间戳格式的日期字段的表(例如:2016-11-01 00:00:00)。我希望能够:

应用数学运算减去1天根据今天的时间窗口(130天)进行筛选显示时不包含邮票的hh/mm/ss部分(2016-10-31)

当前起始查询:

select org_id, count(accounts) as count, ((date_at) - 1) as dateat 
from sourcetable 
where  date_at <= now() - 130
group by org_id, dateat

第1行上的((date_at)-1)子句导致:

[42883]错误:操作符不存在:时间戳没有时区提示:没有匹配给定名称和参数类型的操作符。您可能需要添加显式类型强制转换。位置:69

now()子句生成类似的消息:

[42883] ERROR: operator不存在:timestamp with time zone - 提示:没有匹配给定名称和参数类型的操作符。您可能需要添加显式类型强制转换。位置:……

关于类型转换的在线指南毫无帮助。欢迎输入。

当前回答

对它使用INTERVAL类型。例句:

--yesterday
SELECT NOW() - INTERVAL '1 DAY';

--Unrelated: PostgreSQL also supports some interesting shortcuts:
SELECT 
    'yesterday'::TIMESTAMP, 
    'tomorrow'::TIMESTAMP, 
    'allballs'::TIME AS aka_midnight;

您可以执行以下操作:

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

技巧1

可以追加多个操作数。例:如何得到当月的最后一天?

SELECT date_trunc('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH - 1 DAY';

技巧2

你也可以使用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);

更多信息:

日期/时间函数和操作符

dattype -datetime(特殊值)。

2017-09-06 17:12:18

其他回答

您可以将TIMESTAMP转换为DATE，这允许您从中减去INTEGER。

例如，to get yesterday:

now()::DATE - 1

所以你的问题会变成:

SELECT org_id, date_at::DATE - 1 AS dateat, COUNT(accounts) AS count
FROM sourcetable 
WHERE date_at <= NOW()::DATE - 130
GROUP BY 1, 2

2021-05-07 21:04:54