Questions about daterange() function

From: Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com>
To: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Questions about daterange() function
Date: 2014-06-26 00:53:00
Message-ID: CAD3a31W5tTM1HGiJXkh+U6+toMGHB7wBDfEQotDhu2uCCP5few@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Thanks in advance!

Ken

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2014-06-26 01:12:25 Re: Questions about daterange() function
Previous Message John Lumby 2014-06-25 23:05:53 Re: Extended Prefetching using Asynchronous IO - proposal and patch