本文介绍了从两个时间戳创建PostgreSQL`tsrange`的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试在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 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:


  1. 如果我尝试添加 :: tsrange 在查询结束之前,解释器抱怨:

  1. If I try and add a ::tsrange right before the end of the query, the interpreter complains that:

错误:无法将类型记录转换为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.


推荐答案

使用您的查询可能很简单:

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`的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-11 22:13