Finding data that falls within a date range for a date range

The title of this is horribly confusing, but hopefully this is helpful for some people. The problem I was trying to solve is I have a report that shows where travelers are based on certain dates. For example a client requests a report to know where all of their travelers are that are traveling 9/1 – 9/2. Given that we know the departure and arrival dates of their trip this is pretty straightforward.

Given that their departure date is stored in depDateTime and their return date is stored in arvDateTime and startDate and endDate are timestamps then this will return all users who are traveling during 9/1 – 9/2

WHERE ((depDateTime BETWEEN #startDate# AND #endDate#)
    OR (arvDateTime BETWEEN #startDate# AND #endDate#))

This works great for most trips, but what if the person departs on 8/31 and returns on 9/3? The current WHERE statement will not return them as a result. I fiddled with dozens of scenarios until I realized it’s pretty simple. For the user to not show with the current logic their trip needs to begin before the startDate and end after the endDate, so it required one additional OR statement

WHERE ((depDateTime BETWEEN #startDate# AND #endDate#)
    OR (arvDateTime BETWEEN #startDate# AND #endDate#)
    OR (depDateTime < #startDate# AND arvDateTime > #endDate#))

Using hibernate default schema as a variable

Given you have set up a default_schema in your hibernate configuration

      <property name="hibernate.default_schema">mySchema</property>

Rather than writing a native SQL query like SELECT x FROM mySchema.tableName you can write your query as SELECT x FROM {h-schema}tableName rather than trying to do a find/replace when your schema name inevitably changes. Note that the . after the schema name is not only not required it will not work if it is added.