Thread: Questions about daterange() function

On 06/25/2014 05:53 PM, Ken Tanzer wrote:
> Hi.  I've got lots of tables with start and end dates in them, and I'm
> trying to learn how to work with them as date ranges (which seem
> fantastic!).  I've noticed that the daterange() function seems to create
> ranges with an inclusive lower bound, and an exclusive upper bound.  For
> example:
>
> SELECT
>   reg_spc_date,
>   reg_spc_date_end,
>   daterange(reg_spc_date,reg_spc_date_end)
> FROM reg_spc
> LIMIT 5;
>
>   reg_spc_date | reg_spc_date_end |        daterange
> --------------+------------------+-------------------------
>   2012-04-05   | 2013-10-21       | [2012-04-05,2013-10-21)
>   2013-10-28   |                  | [2013-10-28,)
>   2013-11-01   |                  | [2013-11-01,)
>   2012-10-19   | 2013-11-01       | [2012-10-19,2013-11-01)
>   2005-03-29   | 2013-10-31       | [2005-03-29,2013-10-31)
> (5 rows)
>
> So here are my questions:
>
> 1)  Is there anyway to control this behavior of daterange(), or is it
> just best to (for example) add 1 to the upper bound argument if I want
> an inclusive upper bound?
>
> 2)  This is purely cosmetic, but is there anyway to control the output
> formatting of a daterange to show the upper bound as inclusive?  So that
> daterange(d1,d2) would display as [d1,d2-1] rather than [d1,d2)?
>
> 3)  I couldn't find this discussed in the documentation, and
> specifically didn't find the daterange() function documented, including
> on this page where I might have expected it:
> http://www.postgresql.org/docs/9.3/static/functions-range.html.  Is it
> somewhere else where I'm not finding it?

What version of Postgres are you using?

>
> Thanks in advance!
>
> Ken
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Ken Tanzer wrote
> Hi.  I've got lots of tables with start and end dates in them, and I'm
> trying to learn how to work with them as date ranges (which seem
> fantastic!).  I've noticed that the daterange() function seems to create
> ranges with an inclusive lower bound, and an exclusive upper bound.  For
> example:
>
> SELECT
>  reg_spc_date,
>  reg_spc_date_end,
>  daterange(reg_spc_date,reg_spc_date_end)
> FROM reg_spc
> LIMIT 5;
>
>  reg_spc_date | reg_spc_date_end |        daterange
> --------------+------------------+-------------------------
>  2012-04-05   | 2013-10-21       | [2012-04-05,2013-10-21)
>  2013-10-28   |                  | [2013-10-28,)
>  2013-11-01   |                  | [2013-11-01,)
>  2012-10-19   | 2013-11-01       | [2012-10-19,2013-11-01)
>  2005-03-29   | 2013-10-31       | [2005-03-29,2013-10-31)
> (5 rows)
>
> So here are my questions:
>
> 1)  Is there anyway to control this behavior of daterange(), or is it just
> best to (for example) add 1 to the upper bound argument if I want an
> inclusive upper bound?

See link for question #3; namely use the three-arg version of daterange
(type,type,text)


> 2)  This is purely cosmetic, but is there anyway to control the output
> formatting of a daterange to show the upper bound as inclusive?  So that
> daterange(d1,d2) would display as [d1,d2-1] rather than [d1,d2)?

Not easily - you could write a custom type with the desired canonical form.


> 3)  I couldn't find this discussed in the documentation, and specifically
> didn't find the daterange() function documented, including on this page
> where I might have expected it:
> http://www.postgresql.org/docs/9.3/static/functions-range.html.  Is it
> somewhere else where I'm not finding it?

Yes, the documentation could maybe use some work on this topic.  The
relevant information is provided at:

http://www.postgresql.org/docs/9.3/interactive/rangetypes.html

See especially: 8.17.2 & 8.17.6

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Questions-about-daterange-function-tp5809274p5809277.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.