问题描述
我正在尝试在 postgresql 查询中创建 tsrange
(上周四到上周四),但出现转换错误.
I am trying to create a tsrange
(last Thursday to the previous Thursday) in a postgresql query but I get cast errors.
这就是我到目前为止所得到的(从 this SO question 开始).
This is what I have got so far (starting off from this SO question).
WITH past_week AS (
SELECT date_trunc('day', NOW() + (s::TEXT || ' day')::INTERVAL)::TIMESTAMP(0) AS day
FROM generate_series(-7, 0, 1) AS s)
SELECT (
date_trunc('day', (SELECT day FROM past_week WHERE EXTRACT(DOW FROM day) = '4') - '7 day'::INTERVAL),
date_trunc('day', (SELECT day FROM past_week WHERE EXTRACT(DOW FROM day) = '4')));
这是结果(正确的值,但不是格式,因为它不是范围):
And this is the result (correct value, but not format, since it's not a range):
row
-----------------------------------------------
("2015-10-29 00:00:00","2015-11-05 00:00:00")
(1 row)
现在,有 2 个主要问题困扰着我:
Now, there are 2 main things that bug me:
如果我尝试在查询结束之前添加一个
::tsrange
,解释器会抱怨:
错误:无法将类型记录转换为 tsrange第 6 行:...ROM past_week WHERE EXTRACT(DOW FROM day) = '4')))::tsrange;
ERROR: cannot cast type record to tsrangeLINE 6: ...ROM past_week WHERE EXTRACT(DOW FROM day) = '4')))::tsrange;
我很想避免重复,但我对 SQL 的了解并不那么精通.任何改进都非常受欢迎.
I would love to avoid repetition, but I'm not that proficient in SQL to know how. Any improvement is more than welcome.
推荐答案
Use tsrange()
constructor:
WITH past_week AS (
SELECT date_trunc('day', NOW() + (s::TEXT || ' day')::INTERVAL)::TIMESTAMP(0) AS day
FROM generate_series(-7, 0, 1) AS s)
SELECT tsrange(
date_trunc('day',
(SELECT day FROM past_week
WHERE EXTRACT(DOW FROM day) = '4') - '7 day'::INTERVAL),
date_trunc('day',
(SELECT day FROM past_week
WHERE EXTRACT(DOW FROM day) = '4')));
tsrange
-----------------------------------------------
["2015-10-29 00:00:00","2015-11-05 00:00:00")
(1 row)
使用 CURRENT_DATE
您的查询可能很简单:
Using CURRENT_DATE
your query may be as simple as:
WITH previous_thursday AS (
SELECT CURRENT_DATE- EXTRACT(DOW FROM CURRENT_DATE)::int+ 4 AS thursday
)
SELECT tsrange(thursday- '7d'::INTERVAL, thursday)
FROM previous_thursday;
这篇关于从两个时间戳创建 PostgreSQL `tsrange`的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!