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.
