3 ms·

This sounds like an Office Space-esque rounding error. I recently wrote some code that requests all entries in a single day. The time query range is 00:00:00

This sounds like an Office Space-esque rounding error. I recently wrote some code that requests all entries in a single day. The time query range is 00:00:00 to 23:59:59.999999999. I haven’t checked if nanosecond precision is sufficient for the timestamp source (labview requesting datetime from Windows 7), but I’m under the impression it is. If I was handling something higher stakes like finances, I would tack on a few more 9s.

corty 6y ago
Well then, let me introduce you to the regularly occurring 23:59:60: https://en.m.wikipedia.org/wiki/Leap_second https://en.m.wikipedia.org/wiki/Leap_second
- imglorp 6y ago
  Some of the cloud hosting NTP environments abolish leaps by smearing them out over a long period. That's one way to help out application code unprepared for them. More surprising is that a number of expensive, precision, commercial GPS chips do NOT all handle leap seconds the same way. I worked in a shop that used about three different ones for their PPS and clock outputs and we got about three different behaviors from them. Evidently the leap seconds are broadcast in supplementary data and then applied on chip when it produces a UTC output.
- lmilcin 6y ago
  No server environment will ever see it. Any sane person will immediately understand this is going to break too much stuff and it is not worth educating thousands of developers. Everybody just smears it over some period of time.
pwagland 6y ago
Honestly, it's simpler than that. Instead of doing <= for your end time, simply say <. That is, give me events where time >=30/1/2020 00:00:00 and time < 31/1/2020 00:00:00.
- willis936 6y ago
  Tidy. Thanks. It needs an extra line to properly establish what the next day is, but this is much more robust. For a larger set of reasons related to how data is being stored, the base date variable is a string in format yyyyMMdd. dateNext = sprintf(‘%s’, datetime(date, ‘format’, ‘uuuuMMdd’) + days(1));
- lmilcin 6y ago
  Yes, that is correct solution. You know what the problem is? The developer wants to have events (like trades, transactions, log lines, users signed up, etc.) from a particular day and for some reason they fear/dislike having the next day. For some reason the 31/1/2020 00:00:00 is bad so the will prefer 30/1/2020 23:59:59. I honestly don't know where this is coming from but it is real and I spend literally tens of hours trying to explain this to people, sometimes with no avail. Actually, I know where this is coming from. This is mistaken attempt at addressing the last second. How do you design system that filters stuff by giving range of dates? Go to any reservation system and you will see that to select just today, you need to select from=today and to=today instead of from=today and to=tomorrow. I think fundamentally, this is mental model that causes people to think about the time not as a continuum but rather in chunks of some unit. Depending on the type of event they will select best-matching unit (typically year, day, minute or second) and treat it as atomic with the timestamp of beginning of the time duration representing entire duration. So in those minds, 23:59:59 represents entire last second of the day and there is no problem. 00:00:00 would represent the first second of the next day and that would be a problem. Unfortunately, this is wrong mental model for things that happen on continuum of time or when somebody needs to block things using different units. It only really works for things that are really managed in blocks of a given unit. For example, I can understand specifying accounting date range as from 1st to 31st of the month. This is right. Accounting date is no date at all, it is a label for a set of events that through some rules were associated with the label.

corty 6y ago

Well then, let me introduce you to the regularly occurring 23:59:60: https://en.m.wikipedia.org/wiki/Leap_second https://en.m.wikipedia.org/wiki/Leap_second

imglorp 6y ago
Some of the cloud hosting NTP environments abolish leaps by smearing them out over a long period. That's one way to help out application code unprepared for them. More surprising is that a number of expensive, precision, commercial GPS chips do NOT all handle leap seconds the same way. I worked in a shop that used about three different ones for their PPS and clock outputs and we got about three different behaviors from them. Evidently the leap seconds are broadcast in supplementary data and then applied on chip when it produces a UTC output.
lmilcin 6y ago
No server environment will ever see it. Any sane person will immediately understand this is going to break too much stuff and it is not worth educating thousands of developers. Everybody just smears it over some period of time.

pwagland 6y ago

Honestly, it's simpler than that. Instead of doing <= for your end time, simply say <. That is, give me events where time >=30/1/2020 00:00:00 and time < 31/1/2020 00:00:00.

willis936 6y ago
Tidy. Thanks. It needs an extra line to properly establish what the next day is, but this is much more robust. For a larger set of reasons related to how data is being stored, the base date variable is a string in format yyyyMMdd. dateNext = sprintf(‘%s’, datetime(date, ‘format’, ‘uuuuMMdd’) + days(1));
lmilcin 6y ago
Yes, that is correct solution. You know what the problem is? The developer wants to have events (like trades, transactions, log lines, users signed up, etc.) from a particular day and for some reason they fear/dislike having the next day. For some reason the 31/1/2020 00:00:00 is bad so the will prefer 30/1/2020 23:59:59. I honestly don't know where this is coming from but it is real and I spend literally tens of hours trying to explain this to people, sometimes with no avail. Actually, I know where this is coming from. This is mistaken attempt at addressing the last second. How do you design system that filters stuff by giving range of dates? Go to any reservation system and you will see that to select just today, you need to select from=today and to=today instead of from=today and to=tomorrow. I think fundamentally, this is mental model that causes people to think about the time not as a continuum but rather in chunks of some unit. Depending on the type of event they will select best-matching unit (typically year, day, minute or second) and treat it as atomic with the timestamp of beginning of the time duration representing entire duration. So in those minds, 23:59:59 represents entire last second of the day and there is no problem. 00:00:00 would represent the first second of the next day and that would be a problem. Unfortunately, this is wrong mental model for things that happen on continuum of time or when somebody needs to block things using different units. It only really works for things that are really managed in blocks of a given unit. For example, I can understand specifying accounting date range as from 1st to 31st of the month. This is right. Accounting date is no date at all, it is a label for a set of events that through some rules were associated with the label.