3 ms·

Time is one of the easiest ways to find a bug in a backend app. Search for "23:59:59" or any use of local server time and you got yourself a bug. I work for on

Time is one of the easiest ways to find a bug in a backend app. Search for "23:59:59" or any use of local server time and you got yourself a bug. I work for one of the largest banks in the world and I get to see a lot of code for some mundane backend stuff. For some reasons, people think that the day has always 23 hour, 59 minutes and 59 seconds and that there is a one second delay between one day ending and another starting. Then they are incredulous some trades have vanished. There was a serious movement to stop data flowing around midnight until I reminded everybody that midnight happens 24 times a day (and not even that, there are fractional timezones, DST, etc. to complicate matters).

willis936 6y ago
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.
CodesInChaos 6y ago
I generally store those as midnight of the next day, but figuring out when to display those as 23:59 of the previous day and when to display them as 00:00 of the next day can be tricky.
- jhardy54 6y ago
  I don't think 23:59:59 is the same as midnight, that sounds like an error to me.
  - CodesInChaos 6y ago
    Users often find half open intervals confusing, so it often makes sense to render an interval as ending one tick before it actually ends (especially when the boundary is at midnight). We introduced this change after frequent complaints about our invoices when we still rendered a month as "01.03.2020 00:00 - 01.04.2020 00:00", while people are fine with "01.03.2020 00:00 - 31.03.2020 23:59" despite the minor inaccuracy.
jhardy54 6y ago
> Search for "23:59:59" or any use of local server time and you got yourself a bug. I thought this meant to search the logs / database and I was absolutely horrified that maybe I didn't understand how time works. I think you're talking about hardcoding 23:59:59 as EOD in the source code though, right?
- lmilcin 6y ago
  Yes. 23:59:59 is used when somebody wants to get end of day but doesn't know to take beginning of current day and add 1 day to it. End of today is actually 00:00:00 of tomorrow. For some reason developers don't want to have tomorrow's date in the timestamp when they think about events happening today but this is ok, because it should be used as upper limit (ie. with some kind of less than operator).

willis936 6y ago

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.

CodesInChaos 6y ago

I generally store those as midnight of the next day, but figuring out when to display those as 23:59 of the previous day and when to display them as 00:00 of the next day can be tricky.

jhardy54 6y ago
I don't think 23:59:59 is the same as midnight, that sounds like an error to me.
- CodesInChaos 6y ago
  Users often find half open intervals confusing, so it often makes sense to render an interval as ending one tick before it actually ends (especially when the boundary is at midnight). We introduced this change after frequent complaints about our invoices when we still rendered a month as "01.03.2020 00:00 - 01.04.2020 00:00", while people are fine with "01.03.2020 00:00 - 31.03.2020 23:59" despite the minor inaccuracy.

jhardy54 6y ago

> Search for "23:59:59" or any use of local server time and you got yourself a bug. I thought this meant to search the logs / database and I was absolutely horrified that maybe I didn't understand how time works. I think you're talking about hardcoding 23:59:59 as EOD in the source code though, right?

lmilcin 6y ago
Yes. 23:59:59 is used when somebody wants to get end of day but doesn't know to take beginning of current day and add 1 day to it. End of today is actually 00:00:00 of tomorrow. For some reason developers don't want to have tomorrow's date in the timestamp when they think about events happening today but this is ok, because it should be used as upper limit (ie. with some kind of less than operator).