从时间戳记日期减去1天 [英] Subtracting 1 day from a timestamp date

问题描述

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

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:

1. 应用数学运算符减去1天
2. 根据今天-130天的时间窗口对其进行过滤
3. 不带邮票的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)子句的结果为:

The ((date_at)-1) clause on line 1 results in:

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

[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

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

The now() clause spawns a similar message:

[42883]错误:运算符不存在:带时区的时间戳-整数提示:没有运算符匹配给定的名称和参数类型.您可能需要添加显式类型转换.位置:...

[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: ...

键入强制类型转换的在线指南绝对无济于事.感谢您的投入.

Online guides to type casts are singularly unhelpful. Input is appreciated.

推荐答案

使用 INTERVAL 类型.例如:

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

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

然后您可以执行以下操作:

You can do the following then:

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;

提示

提示1

您可以追加多个操作数.例如:如何获取本月的最后一天?

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

提示2

您还可以使用 make_interval 函数创建间隔，该间隔在需要在运行时创建间隔(不使用文字)时很有用:

Tip 2

You can also create an interval using make_interval function, useful when you need to create it at runtime (not using literals):

SELECT make_interval(days => 10 + 2);
SELECT make_interval(days => 1, hours => 2);
SELECT make_interval(0, 1, 0, 5, 0, 0, 0.0);

更多信息:

日期/时间函数和运算符

数据类型-日期时间(特殊值).

这篇关于从时间戳记日期减去1天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！