Rounding at the start

At a recent consulting client I was working on fixing a few issues with their in app calendar. The calendar has all of the usual features such as adding short, all day events and then displaying them in a calendar view allowing day, 3 day or week views. This is a surprisingly difficult endeavour in both the front end and back end.

One of the issues was that all day events were appearing on the wrong day. So adding an all day event on Tuesday would create an all day event starting on Monday and lasting two days. Given this it sounded like something weird was happing with the starting date. But given that it now spread two days I was very confused as I expected an all day event to only store the start date and the length. So how was the length changing? Also, speaking to a colleague who had looked into this before they said they had spent some time before and narrowed down the location of the issue to the database. But that sounded crazy as we are using MS SQL so a very well battle tested database and we shouldn’t be doing anything weird. So I thought it would be best to take a fresh look and start from the beginning.

So the first thing to do was to try and recreate it locally. This was luckily very easy and worked - I mean didn’t work - exactly as described. I tried a few different versions of all day events. But they all had the same issue. So we needed to start the actual work.

Digging into the network transfer from the UI to the backend you could see that all events were the same sending a start date and time and and ending date and time. For the all day event the event started at midnight and ended at one second before midnight. I could see the correct values were being sent for the all day event but this API didn’t match my expectations. The response from the API also had the invalid start date but the time was still correct and it returned the events ID.

Now, into the API. This is a calendar specific microservice so luckily wasn’t too big. Also its pretty straightforward using the patterns found throughout the rest of the microservices - NestJS and TypeORM. So it was easy to see that how the data flowed through the application and how it was saved. The TypeORM entity defined which table the data lived in so I opened up DBeaver and queried the events table and could see for the all day events I had created were exactly like we saw in the UI - they had the wrong date.

So either, the problem lies in either what the API’s input, our processing or what we are sending to the DB. I added logging at the first point we receive the start date time and the place where we pass it to TypeORM to save. At both of those places it was correct! Could it really be MS SQL?!

So there is still one layer between us and MS SQL - TypeORM. Now TypeORM is a pretty popular library in of itself - not as battle tested as MS SQL but I definitely wouldn’t expect it to be the source of the issue but the logging proves it isn’t us. So I googled it and found an issue that matched it exactly node-mssql v5 incorrectly stores Dates. So the database adapter that TypeORM relies on mssql it had an issue where it would incorrectly round dates within the first 3 seconds of the day brilliantly deduced by @imnotjames. The mssql issue had been resolved but the newly released version 7 alphas of mssql were not compatible with TypeORM.

So this meant that we had to tackle this on two fronts. Firstly we needed to put a workaround in to the calendar microservice so that it would take any datetime this was pretty straightforward. A small PR to wrap any save of a date in the calendar service in a function that checked if the datetime was close to midnight and then add a minute. Secondly I created a PR that fixed the pretty minor compatibility issues between TypeORM and mssql v7.

This morning I can happily say that the PR has been merged and so will be in the next release of TypeORM and we can remove the workaround in the calendar micro service.

I’m pretty happy with how this went and got to dig into the internals of some pretty core parts of our app and the libraries we depend on learning a lot in the process. The fact this is all open and I have the ability to fix issues that we encounter is really empowering. But lets hope I don’t have to do it too often 🤞