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)

I feel you...

One of my services connects multiple systems. Some with timezone information some without. It is all a big guessing game ... Add some misconfigurations of VMs and you get some very interesting errors.

After having tests work on my computer but mysteriously fail on test server I discovered that test server was set to UTC and tests that depended on having test data return the same time/date failed. Since it was not critical to test those edge cases we just offset times in test cases so that they didn't overflow into next day in our test server. It never gets boring when working with time and date.

I hate everything having to do with times, dates, and languages. It's like we are used to rigor and logic and human invented systems from millennia ago are anything but...

Timezone programming sucks. The philosophy I got by is, to store/track/log everything in UTC. When it is displayed, then change the format to the local user timezone IF NEEDED. For example if it's like network device logs or security events, one timezone (UTC normally) should be used, not changed for every user. Convert the reverse for input from client, to back end.

Classic Tom Scott agrees.

Tom Scott has entered the chat

Why are you migrating from DATE to DATETIME2? If the original data didn't need a time component then I don't see why you should add one.