Hacker News Reader

Comment by TooCreative | Hacker News Reader

Additionally, turning (startDate, endDate) into a set of dates will make the code more complex in some places. Before:

    SELECT event FROM events WHERE endDate<"2021"

After:

    Whatever additional complexity you
    add to your codebase to query end
    dates.

View on HN

It's not hugely complex although definitely more than the first example. I guess it depends whether it's offset by the benefits...

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=50e6a963cd1db0...

(YMMV, obvs.)

It's not that much more complex: if you do

    SELECT event FROM events WHERE startDate < "2021"

then all but one of the results (the one with the greatest `startDate`) will also have an implicit end-date prior to 2021.

Until business tells you that endDate is not necessarily greater than startDate. <- real world experience

Sounds interesting can you explain more about it?

Doing all the logic in SQL requires more complexity using subqueries.

It gets uglier if you need to find the contract valid on a certain date based off of a join.

These issues can be covered up with code as it will a easier to have reusable functions, but it makes the job of a data analysts much more difficult and error prone.

Uglier than having to find the record after the one you're inserting (so you can determine your new record's end date from the subsequent record's start date) and the record before (so you can modify its end date to match your new record's start date)?

This is fine for an open-ended query like the one given, because you still receive all the relevant data. But if you're looking at a range, for the same reason you have one extra at the end, you also have one missing at the beginning. And you can't just filter away missing data.

Reply on news.ycombinator.com