PostgreSQL tsrange - 重叠忽略等效范围?(PostgreSQL tsrange — overlap ignores equivalent ranges?)
我的Rails应用程序中有一个查询,如下所示:
# Returns any records whose period intersect the specified time # @see https://www.postgresql.org/docs/9.3/static/rangetypes.html # @see https://www.postgresql.org/docs/9.3/static/functions-range.html # @note The '()' means exclude both sides of the range # # @param period_start [DateTime,Time] Start of the range # @param period_end [DateTime,Time] End of the range scope :overlapping, ->(period_start, period_end) { where( "#{table_name}.period && tsrange(:period_start, :period_end, '()')", period_start: Time.at(period_start.to_i), # Caps precision to the second period_end: Time.at(period_end.to_i) # Caps precision to the second ).distinct }但是,这里的问题是不会返回任何具有相同周期作为由period_start和period_end创建的范围的记录。 这是为什么? 我认为重叠检查是否有任何交叉点?
I have a query in my Rails app that looks like this:
# Returns any records whose period intersect the specified time # @see https://www.postgresql.org/docs/9.3/static/rangetypes.html # @see https://www.postgresql.org/docs/9.3/static/functions-range.html # @note The '()' means exclude both sides of the range # # @param period_start [DateTime,Time] Start of the range # @param period_end [DateTime,Time] End of the range scope :overlapping, ->(period_start, period_end) { where( "#{table_name}.period && tsrange(:period_start, :period_end, '()')", period_start: Time.at(period_start.to_i), # Caps precision to the second period_end: Time.at(period_end.to_i) # Caps precision to the second ).distinct }However, the problem here is that any records who have the SAME period as the range created by period_start and period_end are not returned. Why is that? I thought overlap checked if there was any intersection at all?
最满意答案
但是,这里的问题是不会返回任何具有相同周期作为由period_start和period_end创建的范围的记录。
这不是真的。 不确定你的问题是什么,但事实并非如此。
SELECT tsrange(x,y), tsrange(x,y) && tsrange(x,y) AS overlaps FROM ( VALUES ('yesterday'::timestamp, 'today'::timestamp) ) AS t(x,y); tsrange | overlaps -----------------------------------------------+---------- ["2017-04-24 00:00:00","2017-04-25 00:00:00") | t (1 row)However, the problem here is that any records who have the SAME period as the range created by period_start and period_end are not returned.
That's not true. Not sure what your problem is, but that's not it.
SELECT tsrange(x,y), tsrange(x,y) && tsrange(x,y) AS overlaps FROM ( VALUES ('yesterday'::timestamp, 'today'::timestamp) ) AS t(x,y); tsrange | overlaps -----------------------------------------------+---------- ["2017-04-24 00:00:00","2017-04-25 00:00:00") | t (1 row)更多推荐
发布评论