My favourite is battling with people who use 23:59:59.
Where I work (banks and other financial institutions) there is frequently a need to check whether something happened within a particular day. Or maybe select records from the database for a day, or do some other kind of logic or filtering.
For some unknown reason, most people decide that best way to do this is to take start date as the beginning of the period and then add to it 23 hours, 59 minutes and 59 seconds and use that as the end of the period.
Explanations that they are missing a whole second do not seem to be working. People are absolutely convinced they are doing this correctly.
I thought about this for a long time and I arrived at an explanation.
It seems that some people use the time as a label for a span of time of unit length. March 21st is a label for an entire day. 12 pm is a label for an entire hour that starts at 12pm and lasts an hour, etc. 2021 is a whole year.
And in some contexts it makes sense. Way say something happened on 12th of January -- we use Jan, 12th as a label for an entire day that we would otherwise have to denote with two timestamps. But in some contexts what we need is an exact point in time, a timestamp. And here is where a lot of people just don't think / can't recognise a difference between timestamps and labels for a span of time.
If you use a wrong model then yes, the day starts with a second labeled 00:00:00 and ends with a second labeled 23:59:59.
Except that's not how most of the underlying software works. Most software in this case expects two exact timestamps to denote the end of the span of time. And 23:59:59 is just 1 second shy of the actual end of day which means that, even if we are missing an entire second of the day, most of the time everything seems to work fine.
Unless you are large bank and you have millions of transactions all over the clock that have to accumulated exactly. Then yes, it makes a lot of difference.
Another explanation is that people don't seem to be comfortable with the concept of selecting items from between 00:00:00 of one day and 00:00:00 of the next because they are seeing another date.
This is why experienced programmers will choose closed-open intervals by default -- they sidestep many of these pitfalls. For your example, you would do
... and date >= '2020-02-13' and date < '2020-02-14' -- notice: >= and <
Since this also applies to real numbers, maybe it is easier to keep in mind with an example in that realm. You would never check for a real number in a given range with:
... and X >= 3.0 and X <= 3.9 // or 3.99, or 3.999999
And the scales just fell from my eyes. I'm guilty of this in the system I develop at work. Whenever I want to show the user all widgets that were made on the date they selected, I will just do something like
DATE(widget_made) = :user_date
Which does what I want and avoids the problem you brought up. But sometimes I have some more complicated logic that falls down if I try to compare datewise. So I manually create the DateTime ranges, and use 23:59:59 as the end of the range. So something like
widget_made BETWEEN :user_date '00:00:00' AND :user_date '23:59:59'
Anything that came off the line at 23:59:59.3421 will be excluded. Fortunately for me, I don't think that has ever actually happened. But now I know to be on the lookout, and use proper date-handling tools to ensure correctness.
Right. The best way to think about it is that any implicit values which are required to univocally define a timestampz should be considered as NULL. So 2022-02-12 should mean “some time during such day in some time zone”.
Where I work (banks and other financial institutions) there is frequently a need to check whether something happened within a particular day. Or maybe select records from the database for a day, or do some other kind of logic or filtering.
For some unknown reason, most people decide that best way to do this is to take start date as the beginning of the period and then add to it 23 hours, 59 minutes and 59 seconds and use that as the end of the period.
Explanations that they are missing a whole second do not seem to be working. People are absolutely convinced they are doing this correctly.
I thought about this for a long time and I arrived at an explanation.
It seems that some people use the time as a label for a span of time of unit length. March 21st is a label for an entire day. 12 pm is a label for an entire hour that starts at 12pm and lasts an hour, etc. 2021 is a whole year.
And in some contexts it makes sense. Way say something happened on 12th of January -- we use Jan, 12th as a label for an entire day that we would otherwise have to denote with two timestamps. But in some contexts what we need is an exact point in time, a timestamp. And here is where a lot of people just don't think / can't recognise a difference between timestamps and labels for a span of time.
If you use a wrong model then yes, the day starts with a second labeled 00:00:00 and ends with a second labeled 23:59:59.
Except that's not how most of the underlying software works. Most software in this case expects two exact timestamps to denote the end of the span of time. And 23:59:59 is just 1 second shy of the actual end of day which means that, even if we are missing an entire second of the day, most of the time everything seems to work fine.
Unless you are large bank and you have millions of transactions all over the clock that have to accumulated exactly. Then yes, it makes a lot of difference.
Another explanation is that people don't seem to be comfortable with the concept of selecting items from between 00:00:00 of one day and 00:00:00 of the next because they are seeing another date.