4 ms·

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

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.

dghf 6y ago
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.
- peteradio 6y ago
  Until business tells you that endDate is not necessarily greater than startDate. <- real world experience
  - NegativeLatency 6y ago
    Sounds interesting can you explain more about it?
- mobjack 6y ago
  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.
  - dghf 6y ago
    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)?
- jdmichal 6y ago
  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.
zimpenfish 6y ago
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=50e6a963cd1db0ae4108e78a3fc57f10 https://dbfiddle.uk/?rdbms=postgres_11&fiddle=50e6a963cd1db0... (YMMV, obvs.)

dghf 6y ago

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.

peteradio 6y ago
Until business tells you that endDate is not necessarily greater than startDate. <- real world experience
- NegativeLatency 6y ago
  Sounds interesting can you explain more about it?
mobjack 6y ago
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.
- dghf 6y ago
  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)?
jdmichal 6y ago
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.

zimpenfish 6y ago

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=50e6a963cd1db0ae4108e78a3fc57f10 https://dbfiddle.uk/?rdbms=postgres_11&fiddle=50e6a963cd1db0... (YMMV, obvs.)