为什么postgres的daterange的上限函数会返回一个排他界限? [英] Why does postgres' upper range function for a daterange return an exclusive bound?
问题描述
我为此帖子创建了一个sql小提琴: http://sqlfiddle.com /#!15 / 7b5d7 / 1/0
问题#1
为什么插入具有排他界限的 daterange
的记录实际上存储一个包含排他性下限和排他性上限的范围? pg为什么不将它们都存储为包含边界?
问题#2
SELECT upper('[[2016-06-19,2016-06-21)':: daterange)
返回 2016-06-21
。请注意, [
表示包含下限,而)
表示上限。
不应该选择收益上限 2016-06-20
吗?日期是否没有离散的时间间隔?
Re 问题1 :闭门是处理日期范围的标准方法,具有20-25年的学术文献历史。请参见Tom Johnston的 Bitemporal Data 的第24-25页,以及Richard Snodgrass的用SQL开发面向时间的数据库应用程序。
但是我认为原因之一是连续范围没有重叠。如果 a
是 [2016年5月,2016年6月)
和 b
是 [2016年6月,2016年7月)
,他们没有任何一天可共享。因此,它们对齐,而您不必担心它们接触的边缘情况。
请注意,封闭式开放的一个缺点(也许)是您不能指定一个空范围。 [2016年5月,2016年5月)
只是自相矛盾,而 [2016年5月,2016年5月]
是瞬间。 p>
关于问题2 :同样,它本来可以有所不同,但我可以想到使变大的几个优点([ May2016,Jun2016))
返回 Jun2016
:
- 不管范围的分辨率如何,它都会返回相同的结果。
- 它更像是开放端点的数学含义,其中开放端点是可能的
- 它返回与标签匹配的内容,因此可以说并不奇怪。
- 它使您可以轻松地看到两个范围是否满足 :
上(a)=下(b)
。
另外,请注意在Postgres all 中,与时间相关的数据类型是离散的。曾经有一个选项可以使用基于浮点数的时间戳来编译Postgres,但是已经过时了,我从未见过。
I've created a sql fiddle for this post: http://sqlfiddle.com/#!15/7b5d7/1/0
Question #1
Why does inserting a record with a daterange
that has exclusive bounds actually store a range with an inclusive lower bound and an exclusive upper bound? Why doesn't pg store them both as inclusive bounds?
Question #2
SELECT upper('[2016-06-19, 2016-06-21)'::daterange)
returns 2016-06-21
. Note that [
signifies an inclusive lower bound and )
an exclusive upper bound.
Shouldn't selecting the upper bound return 2016-06-20
? Don't dates have have discrete intervals?
Re Question #1: closed-open is the standard way to handle date ranges, with 20-25 years of history in the academic literature. See pp. 24-25 of Bitemporal Data by Tom Johnston, and also Developing Time-Oriented Database Applications in SQL by Richard Snodgrass.
But I think one of the reasons is that consecutive ranges don't have overlap. If a
is [May2016, Jun2016)
and b
is [Jun2016, Jul2016)
, they don't share any days. So they "snap together", and you don't have to worry about edge cases where they touch.
Note that one drawback (maybe) of closed-open is that you can't specify an empty range. [May2016, May2016)
is simply a self-contradiction, whereas [May2016, May2016]
is an instant.
Re Question #2: Again it could have been different, but I can think of several advantages of making upper([May2016, Jun2016))
return Jun2016
:
- It returns the same thing regardless of the resolution of the range.
- It is more like the mathematical meaning of an open endpoint, where it is the only possible answer.
- It returns what matches the "label", so arguably it is less surprising.
- It lets you easily see if two ranges "meet":
upper(a) = lower(b)
.
Also, note that in Postgres all time-related datatypes are discrete. There used to be an option to compile Postgres with float-based timestamps, but it is deprecated and I've never encountered it.
这篇关于为什么postgres的daterange的上限函数会返回一个排他界限?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!