Date ranges seem so simple, yet they can be among the more vexing aspects of data design and SQL reporting. In our databases, we encounter tricky issues such as open end dates, ambiguous boundary definitions, implicit ranges, and related ranges in the same or different tables. Our business managers ask for reports that seem intuitive yet beyond the capabilities of our systems.

In the context of PostgreSQL, I'll illustrate good data designs that handle tricky logical requirements and that alleviate the most common data integrity, development, and SQL reporting performance problems. I'll also review applicable data types, query strategies, and compare programming techniques for difficult date calculations such as unioning, intersecting, and subtracting large sets of date ranges.

(1 hour)

Most of my career (since 1999) has been designing and reporting from medium-sized databases at large public school districts and other government funded agencies, where the government accountability trend has driven increasingly intensive reporting requirements for publicly-funded human services. I authored and managed software for Highline Public Schools in Burien, Washington for the state's Comprehensive Education Data and Research System (CEDARS).

Now I have fun at Command Prompt Inc. consulting with customers on data design, SQL, and related database issues.

• Slide deck
• CEDARS software project
• pgchronos