6 ms·

Date is hard. The example says > BETWEEN '2018-01-01 00:00:00:000' and '2018-12-31 00:00:00:000' So the last day of december will be missing.

quietbritishjim 7y ago
BETWEEN is inclusive, so it will match '2018-12-31 00:00:00:000' but not '2018-12-31 00:00:00:001'. So if the date column actually only stores dates (not times) then it will actually work correctly - certainly using "2019-01-01 00:00:00:000" would give wrong results. It would be safer, and clearer, by doing the half-open comparison explicitly. But I would argue it's BETWEEN that's hard (because it's closed rather than half-open as you'd expect) rather than dates, at least in this case. orderdate >= '2018-01-01 00:00:00:000' AND orderdate < '2019-01-01 00:00:00:000'
- tda 7y ago
  In postgres I would use tsrange to make it very explicit if I want to include the edges or not: '[2018-01-01 00:00:00, 2019-01-01 00:00:00)'::tsrange @> '2018-01-01 00:00:00'::timestamp
  - quietbritishjim 7y ago
    I didn't realise that Postgres has ranges, thanks for that. But, even though I have a background as a pure mathematician, I strongly disagree that "[a, b) includes x" is any more explicit than "x >= a AND x < b". Given that those ranges aren't standard SQL and are much less commonly used than simple inequality operators, I'd stick with inequalities for clarity.
- mFixman 7y ago
  You don't have to put a full timedate inside the comparison string. Writing `BETWEEN '2018-01-01' AND '2019-01-01'` works correctly and it's pretty readable. However, I agree that making `BETWEEN` use closed intervals is one of the many design-level mistakes on SQL.
  - quietbritishjim 7y ago
    `BETWEEN '2018-01-01' AND '2019-01-01'` does not work correctly. It will match '2019-01-01 00:00:00.000'. Assuming that this column only contains dates (i.e. with the time always set to 00:00:00.000) this means that 2019-01-01 will always be matched, one whole extra day than is required. `BETWEEN '2018-01-01' AND '2018-12-31'`, which as you say is just shorthand for what the article says, will correctly match '2018-12-31' (again, assuming the time in all values is precisely 00:00:00.000) and will not '2019-01-01', which is the correct behaviour.
    - mFixman 7y ago
      If the dates are stored in strings, then you are wrong in both counts. '2019-01-01' < '2019-01-01 00:00:00.0000', so that time won't match the date. '2018-12-31' < '2018-12-31 00:00:00.000', to it will not match that either.
      - quietbritishjim 7y ago
        > If the dates are stored in strings The rest of your comment is fairly academic because, surely, the dates are stored as a native date/time type. (Either `date` itself or `timestamp without timezone` - probably the latter given that the literals in the article mention times.) Using strings would both be inefficient cause numerous correctness problems, including, as you just said, the fact that comparison with '2018-12-31' would give different results to comparison with '2018-12-31 00:00:00.000'. There's no reason to assume that the article had chosen this insane path. Just to humour the possibility that someone had, foolishly, used a string for this possibility: Your most recent comment is correct that as strings '2018-12-31' < '2018-12-31 00:00:00.000', so the BETWEEN in my previous comment would not have worked (although it depends, of course, on what exact strings were stored in the column!). But the comparison you suggested in your previous comment would also be incorrect, for the same reason I already objected to: `BETWEEN '2018-01-01' AND '2019-01-01'` will match (the string) '2019-01-01'.

quietbritishjim 7y ago

BETWEEN is inclusive, so it will match '2018-12-31 00:00:00:000' but not '2018-12-31 00:00:00:001'. So if the date column actually only stores dates (not times) then it will actually work correctly - certainly using "2019-01-01 00:00:00:000" would give wrong results. It would be safer, and clearer, by doing the half-open comparison explicitly. But I would argue it's BETWEEN that's hard (because it's closed rather than half-open as you'd expect) rather than dates, at least in this case. orderdate >= '2018-01-01 00:00:00:000' AND orderdate < '2019-01-01 00:00:00:000'

tda 7y ago
In postgres I would use tsrange to make it very explicit if I want to include the edges or not: '[2018-01-01 00:00:00, 2019-01-01 00:00:00)'::tsrange @> '2018-01-01 00:00:00'::timestamp
- quietbritishjim 7y ago
  I didn't realise that Postgres has ranges, thanks for that. But, even though I have a background as a pure mathematician, I strongly disagree that "[a, b) includes x" is any more explicit than "x >= a AND x < b". Given that those ranges aren't standard SQL and are much less commonly used than simple inequality operators, I'd stick with inequalities for clarity.
mFixman 7y ago
You don't have to put a full timedate inside the comparison string. Writing `BETWEEN '2018-01-01' AND '2019-01-01'` works correctly and it's pretty readable. However, I agree that making `BETWEEN` use closed intervals is one of the many design-level mistakes on SQL.
- quietbritishjim 7y ago
  `BETWEEN '2018-01-01' AND '2019-01-01'` does not work correctly. It will match '2019-01-01 00:00:00.000'. Assuming that this column only contains dates (i.e. with the time always set to 00:00:00.000) this means that 2019-01-01 will always be matched, one whole extra day than is required. `BETWEEN '2018-01-01' AND '2018-12-31'`, which as you say is just shorthand for what the article says, will correctly match '2018-12-31' (again, assuming the time in all values is precisely 00:00:00.000) and will not '2019-01-01', which is the correct behaviour.
  - mFixman 7y ago
    If the dates are stored in strings, then you are wrong in both counts. '2019-01-01' < '2019-01-01 00:00:00.0000', so that time won't match the date. '2018-12-31' < '2018-12-31 00:00:00.000', to it will not match that either.
    - quietbritishjim 7y ago
      > If the dates are stored in strings The rest of your comment is fairly academic because, surely, the dates are stored as a native date/time type. (Either `date` itself or `timestamp without timezone` - probably the latter given that the literals in the article mention times.) Using strings would both be inefficient cause numerous correctness problems, including, as you just said, the fact that comparison with '2018-12-31' would give different results to comparison with '2018-12-31 00:00:00.000'. There's no reason to assume that the article had chosen this insane path. Just to humour the possibility that someone had, foolishly, used a string for this possibility: Your most recent comment is correct that as strings '2018-12-31' < '2018-12-31 00:00:00.000', so the BETWEEN in my previous comment would not have worked (although it depends, of course, on what exact strings were stored in the column!). But the comparison you suggested in your previous comment would also be incorrect, for the same reason I already objected to: `BETWEEN '2018-01-01' AND '2019-01-01'` will match (the string) '2019-01-01'.