Learn T-SQL With Erik: Time Zone Confusion
Chapters
- 00:00:00 – Introduction
- 00:02:30 – Time Zone Ambiguity Explained
- 00:05:15 – Example with Non-Existent Time
- 00:07:45 – Daylight Saving Time Ambiguity
- 00:10:30 – Non-Deterministic Nature of AT TIME ZONE
- 00:12:00 – Conclusion
Full Transcript
Erik Darling here with Darling Data. Coming to you late in the day. I had a very fun problem that I had to deal with earlier, and that prevented me from recording according to my usual schedule, but it will be in a written, a formally written blog post tomorrow, you lucky people. Coming to you live with my Jermaine Dupree bobblehead, Atlanta Braves bobblehead. I did not attend this game. I had to wait until I found it on eBay, but now I have my very lucky Jermaine Dupree, Atlanta Braves bobblehead, who will be guiding me through this perilous video, where we’re going to talk about time zone confusion. This material, like a lot of the other videos that I do, comes from the larger corpus of Learn T-SQL with Erik material. You can buy it down in the video description. You can get a hundred bucks off the full course, which is a fantastic deal compared to other training, because you get this for life. Your entire life. Think about how long and arduous your life is going to be. And think about how more long and arduous your life will feel without proper T-SQL training. Ah, it’s, it’s crazy. You ought to buy it. Small investment, make your life feel less long and arduous. I think anyway. Anyway, down in the video description, on top of the link to purchase this very course, you’ll find other helpful links should you choose to interact with me in more interesting ways. You can hire me for consulting. You can buy even more of my training. I have lots more training than just this. You can become a supporting member of the channel for as few as $4 a month. You can, you can say, thanks, Erik. And that’s it. It’s just, it’s just a thank you. You don’t get anything extra. You just say thanks with money, because that’s if that’s how most people do it these days, right? It’s hard to say thanks with coupons. And of course, if you enjoy this content, and you think that other people might enjoy this content, please do like, subscribe, tell a friend, all that fun stuff, so that I feel some sense of obligation to keep recording this. Otherwise, I don’t know what I’m going to do. Just sit around and talk to robots all day.
If you need free SQL Server and or Postgres, including Aurora Postgres database performance monitoring, boy, do I have a deal for you. Steal of a lifetime. Totally free, totally open source monitors both. It’s a grand time for everybody. And does all the stuff you would want it to do. It’s got fancy WPF Windows dashboard. It’s got a fancy web viewer dashboard. You can you can make custom views and stuff. It’s got MCP tools. It’s the works really. It’s amazing what I’m giving away here. And it’s amazing how much money you can save switching to this from your current crappy SQL Server and or Postgres, including Aurora Postgres monitoring tool.
Just think, imagine how happy your company will be if all of a sudden you were like, hey, I’m going to save you many thousands of dollars every year, and we’re going to have a better monitoring tool where we can actually figure things out. And people aren’t just sitting around staring at it like idiots. It just might happen for you, right? Go to code.erikdarling.com, the performance monitor repo. Everything will be explained there. There’s a wonderful reading file. In just a couple short months, I’ve got to book my travel. I will be in Seattle, Washington from November 9th to 11th at Pass Summit West, taking place this year at the Hyatt Regency, Seattle. I hope they have a good buffet, I think.
I won’t be staying there, but I will be presenting there. Never stay in the hotel where the conference is. It might seem like a good idea at first, but it’s not a good idea. That’s why when people follow you to your room, creepy. Anyway, let’s drink some beer, pitchfork some crows, and I don’t know. Buddy, I’m going to chip your teeth on that corn. I’m going to tell you. All right. Anyway, so when it comes to time zone stuff in SQL Server, one thing that is important to note is that SQL Server does track time zones, and it uses the Windows registry. It got better at this, but it still uses the Windows registry for time zone things.
And one thing that you might notice if you ever look at the sys dot time zone info. Now, there’s no DM in there. I put that into DMV names frequently for no apparent reason. All it tells you is the name, the current offset, and if that is in daylight savings time. There’s just standard time. There’s no daylight times listed in here, right? It only tells you standard time, current offset, and if that current offset is being respectful of DST.
Who knows if this will change if America decides to get rid of one or the other? I forget which one we’re getting. I can’t remember if we’re getting rid of the good one or the bad one. It’s tough to keep up with the news these days. But lots of other places have changed this too over the years. And depending on when you started logging time zone information, it could be right, wrong, or somewhere in between.
I did some research. I think there was maybe a Wikipedia article on all the time zone changes over the years. And, boy, people just keep messing with it. I guess it’s just a fun thing that no one can agree on whether to switch times or not. Personally, I’m not a fan of switching times. Not only because it’s confusing and because I still have things that I have to set manually.
Like the watches that make people mad and the times on the microwave and stove, which are not smart appliances, apparently. But anyway, I suppose we should run a query here, shouldn’t we? So if you’re doing DST handling, you do need to consider all of these things, right?
So let’s look at this query, which is, I don’t know, this query is actually a little bit long for my tastes. Eh, look at this thing. 50 lines? Something like that. So at various points in the year, various times in the year, you will have slightly different offsets and whatnots.
But UTC at least stays nice and faithful to what time it is, right? You can’t have a UTC that switches times on you. That would not be fun. This demo wasn’t particularly interesting, but it sets us up to talk about more interesting things later.
Like certain times that never happened and in certain times that happened twice. And if you’ve ever scheduled SQL Server agent jobs and they’ve ever run around a time switch, you’ve felt this pain. So again, UTC time zones just don’t have a daylight thing, right?
They just like, is currently DST? No, because they just don’t. So that’s one reason, again, in favor of storing everything in DST, because if you’re using UT, sorry, storing everything in UTC because there’s no DST.
And so you save your, you diminish your problems by at least two hours on two days a year, depending on, I guess, it depends. Does everyone switch clocks at the same time? I don’t think so.
But DST does create two problems. When you spring forward, the clock jumps immediately from 2 a.m. to 3 a.m. So the hour between 2 and 3 is like it never happened, right?
Back when yours truly was a bouncer in the clubs, this was a confusing time for people. As is the fallback where you might run into an ambiguous time, right? Because is it 1 o’clock or is it 12 o’clock again?
Like, what do we do here, right? It’s nuts, right? So when we spring forward, right, the first 2.30 never existed on 2026.03.08. And so at time zone, just hands you 3.30 to 4 instead, right?
It doesn’t error. It doesn’t say, ah, hey, careful here. It just says, hey, you’re an hour ahead now. Congratulations, right?
So let’s say that the date time that we wanted was this one. Where is my draggy thing? There is my draggy thing. Let’s move you up a little bit. Actually, you know what?
That wasn’t a perfectly good place before. Why did I mess with anything? I’m an idiot sometimes. So this is our date time that we’re asking for. 2026.03.08 at 2.30 in the manana. And when we say, hey, SQL Server, what time is that in Eastern time?
It says 3.30. 2.30 doesn’t exist. I’m sorry.
It’s not here. It might be somewhere. It’s not here, right? So just a fun little thing that might happen to you. Well, one hour and two days a year. One hour, one day a year.
But the second day of the year is even scarier, right? So 1.30 a.m. happens twice on 2026 1101. That’s right around past.
I’m going to have two mornings where I’m confused. So what do you name the second 1.30 a.m.? What do you call a time that you’ve already experienced? Is this just deja vu?
I don’t know. Quite frankly, confused by the whole thing, right? But we have the same local time with two actual real moments in time that happened. And if your table stores that same naive local value, those two rows are indistinguishable forever and ever, right?
You better have some unique constraints, buddy. You better have them, right? So if we run this query again, very long query.
Look at the size of this thing. Gigantic, right? Ah, we got to shrink that in a little bit. Let’s bring that in for a nice hug. Our local time, right?
2026 1101 at 1.30, right? And we say, ah, what about at Eastern time? The first time it happened, right? That’s 1.30 minus 4.
And if we look at that time in UTC, right? What do we see? Well, that’s 5.30 plus zero. Okay.
That seems sensible. But then it happens again at 1.30 minus 5, right? And in UTC, ah, man, you’re going to make me sweat and do all this column clickery, jaggery stuff. Do it!
Now, all of a sudden, it happened again at 6.30, right? So, in Eastern time, when the time switch, it happens once at 1.30. Oh, sorry. It happens once at 5.30 UTC, and then it happens again at 6.30 UTC.
Time was a mistake is really the big deal here, right? But once that transition is behind you, the ambiguity, well, the ambiguity of the moment is gone, but the ambiguity in your data might persist forever, right?
So, you know, once that moment is over, all of a sudden, everything seems back to normal, but the data in your tables might not be back to normal. One other thing to note about at time zone is because of all this foolishness, it is non-deterministic, right?
So, if we try to create a table, and we try to say, we’re going to get ahead of this time zone stuff, we’re going to have good performance, we’re going to stay on top of time zones, and we try to create a persisted computed column, we can’t do that, right?
Because it’s non-deterministic, right? It’s not going to happen for us. So, because it’s not deterministic, that means that, though we can add it as a non-persisted column, that means we cannot index it, because this is also non-deterministic, which is a real, real sad state of affairs.
We can’t even have deterministic time in SQL Server. What kind of fake database is this? Boils the blood.
Anyway, it’s time for me to go. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s written blog post about query store stuff. You’ll love it.
Or hate it. I don’t know. I don’t know what you’re responsible for. But goodbye from me and Jermaine. Alright. Have a good one.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.