Re: Questions about daterange() function - Mailing list pgsql-general

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?