5 ms·

In my opinion BETWEEN should not be used because it is not clear whether the boundaries are open or closed. See also https://wiki.postgresql.org/wiki/Don%27t_D

In my opinion BETWEEN should not be used because it is not clear whether the boundaries are open or closed. See also https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_BETWEEN_.28especially_with_timestamps.29 https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use...

adeelk93 5y ago
I thought the BETWEEN boundaries are always closed. Isn’t that pretty clear? If you’re worried about date part precision in that comparison, you can always TRUNC before you BETWEEN.
- williamdclt 5y ago
  > Isn’t that pretty clear? Nop :) That would be my guess too, but I definitely would not be certain about it, and the dozens of people that are going to read the code won't be either, some of them might make the wrong assumption, and bad things happen. I much prefer to be explicit with `>= <=` too
- marcosdumay 5y ago
  On Postgres, yes. I wouldn't count on every DBMS correctly implement the standard. Specifically, I would expect Oracle to have some crazy concept of "closed" on specific contexts that don't align with anybody's. Anyway, in my opinion it shouldn't be used because the boundaries are both closed, and that's an incredibly bad choice. Even when it doesn't matter you have to go and confirm it doesn't matter.
- LittlePeter 5y ago
  In the real-world world between almost always implies open boundary. Ask a hundred people to draw a point between two lines. I think none will draw a point on the line. When I enter the programming world, I carry over real-world concepts with me, and this where BETWEEN being closed gets confusing to me. Since I'm not a special snowflake, there must be other people like me who could be confused. Hence why I prefer not to use BETWEEN.
  - adeelk93 5y ago
    Oh I was thinking the opposite. I work between Monday and Friday, that’s a closed boundary. I have between 5 and 10 oranges, another closed boundary.
    - simorley 5y ago
      Maybe it's a temporal vs spatial vs cardinal issue. I'm sitting "between or in between" two people ( open boundary or exclusive or non-inclusive ). Seems spatial is always open boundary. Park your car between those two trucks. I walked between two clowns.
      - adeelk93 5y ago
        Ah good explanation. You’re right.

adeelk93 5y ago

I thought the BETWEEN boundaries are always closed. Isn’t that pretty clear? If you’re worried about date part precision in that comparison, you can always TRUNC before you BETWEEN.

williamdclt 5y ago
> Isn’t that pretty clear? Nop :) That would be my guess too, but I definitely would not be certain about it, and the dozens of people that are going to read the code won't be either, some of them might make the wrong assumption, and bad things happen. I much prefer to be explicit with `>= <=` too
marcosdumay 5y ago
On Postgres, yes. I wouldn't count on every DBMS correctly implement the standard. Specifically, I would expect Oracle to have some crazy concept of "closed" on specific contexts that don't align with anybody's. Anyway, in my opinion it shouldn't be used because the boundaries are both closed, and that's an incredibly bad choice. Even when it doesn't matter you have to go and confirm it doesn't matter.
LittlePeter 5y ago
In the real-world world between almost always implies open boundary. Ask a hundred people to draw a point between two lines. I think none will draw a point on the line. When I enter the programming world, I carry over real-world concepts with me, and this where BETWEEN being closed gets confusing to me. Since I'm not a special snowflake, there must be other people like me who could be confused. Hence why I prefer not to use BETWEEN.
- adeelk93 5y ago
  Oh I was thinking the opposite. I work between Monday and Friday, that’s a closed boundary. I have between 5 and 10 oranges, another closed boundary.
  - simorley 5y ago
    Maybe it's a temporal vs spatial vs cardinal issue. I'm sitting "between or in between" two people ( open boundary or exclusive or non-inclusive ). Seems spatial is always open boundary. Park your car between those two trucks. I walked between two clowns.
    - adeelk93 5y ago
      Ah good explanation. You’re right.