Re: Questions about daterange() function

On Wed, Jun 25, 2014 at 6:12 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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?
>

In this particular case 9.3, although also working in 9.2. Both are the
currrent versions.

>
>
>> Thanks in advance!
>>
>> Ken
>>
>>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.