The primary application at my job was…not well written. Originally .NET Framework 3.5 with a strange collection of approaches to MVC. SQL Server for data. I claimed it look like something written by CS students fresh out of college on their first job. Turned out I was close…it was written by 3rd-year CS interns. We’ve fixed and improved a ton (including migrating to .NET 6 earlier this year).
One of the ongoing issues was the use of SMALLDATETIME and DATE fields for everything. We’ve been gradually migrating these to DATETIME2 or DATETIMEOFFSET (we don’t have future dates) as UTC. Because 95% of our usage had been CST/EST, we’ve typically set the time component to 17 hours to get a reasonable noonish time in those zones when we don’t have a better time of day.
Had another round of these today and thought it was going well. Finally got it running and everything was +1 day from where it should be. Spent an hour trying to figure out the issue.
Converting for display incorrectly? No. Serializer not doing what was expected? No. Configured time zone got messed up? No. Brought in our DBA to help me figure it out.
I had added 17 days twice.
Side note: I wish everybody was on UTC time and time zones would forever disappear into the ether.
TLDR; Migrated time wrong because I’m stupid.
One front-end project I did connected to a backend API that had the “time of product launch” stored in local time on the server, which was eastern time. I was to display a “time remaining countdown” on the web page.
At the time, JS wasn’t great at doing anything other than localtime and UTC. (Not sure it’s any better now.)
The DB folks were unwilling to change the date from the backend to UTC.
Great. I’ll just math it over from eastern to UTC on the client, then convert to local.
Of course, the countdown to product launch spanned a shift to daylight saving time. Great. I’ll just add some condition and change the offset on that date.
Tested it like crazy, and it seemed to work.
A couple weeks later with 2 days to launch, the countdown was suddenly off by 2 hours (??!) and I had to sort it out.
Turns out they’d switched over from their dev API server on the east coast to their production API server in mountain time.
Changing one constant in my code fixed everything, but I swore I never wanted to have a universally single event stored in anything but UTC ever again.
Side note: I wish everybody was on UTC time and time zones would forever disappear into the ether.
Everyone who has worked with dates feels this way. I for one have switched to the 24:00 clock and keep the UTC time as a widget on my phone. It won’t happen in our lifetime, but I’ll be damned if I’m not stubborn about it.
(Also as a side note, I work in game dev and I force everything to be in UTC. I don’t care what the PM says, our events all reset midnight UTC)
