SQL:从时间戳日期中减去 1 天 [英] SQL: 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 天
- 根据今天 - 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)
子句导致:
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
类型.例如:
Use the INTERVAL
type to it. E.g:
--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);
更多信息:
这篇关于SQL:从时间戳日期中减去 1 天的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!