Learn T-SQL With Erik: Time Zones and Offsets
Chapters
- 00:00:00 – Introduction
- 00:02:30 – Time Zone Basics
- 00:05:15 – Using AT TIME ZONE and SWITCHOFFSET
- 00:07:45 – Examples of Time Zone Adjustments
- 00:10:10 – Time Zone Storage Best Practices
- 00:12:30 – Time Zone and Clock Changes
- 00:14:00 – Time Zone Query Considerations
- 00:14:55 – Summary and Next Steps
Full Transcript
Erik Darling here with Darling Data, and this week and next are going to be just a couple video weeks for me. It turns out that after being traveling abroad, which is really just a trip because when you have kids and laptops with you, you’re just home somewhere that you like a little bit better. It turns out that you have a lot of stuff to catch up on when you get back, and that’s where I’m at. This week, actually, just earlier today, because it is 9-10. Well, it is September 10th. I just released a new cut of my free SQL Server and Postgres SQL Server monitoring tool. Yes, that even covers Aurora Postgres, the thing that everyone is falling over their willies for. We’ll talk about that a little bit more. I have a bunch of videos that I need to record to talk about where the monitoring tool has gone, all the new stuff in it, because there really is a lot. I’m going to talk this overview a little bit in this video, but this video is going to continue the Learn T-SQL stuff that I did not finish before I went away, and we’re going to finish up talking about time zone stuff.
So after this week and next, we’ll get back into the other stuff. Down in the video description, as per usual, as per my last video, you’ll see all sorts of helpful links. You can hire me for consulting, purchase my wonderful, fairly priced training, become a supporting member of the channel. You can ask me office hours questions for free. I will still be doing office hours. I might even do some extra ones. I was like, I’m going to clear up the office hours queue. I didn’t come close to that. I missed a bunch of stuff. There’s a million questions in there. So I still have to work on that. But as always, please do like, subscribe, tell your friends, tell your enemies, tell all the witches and warlocks that you know. I still have to fix this slide too. My free SQL Server and Postgres performance monitoring tool. You can read more about it at this link over here. There’s also a Postgres. I should probably just add a Postgres page.
I’m going to show you in a second. Totally free, totally open source, no email, no phone home. We are now focusing on the Lite version, which you can just crack open the executable, plug in a server name and immediately start monitoring. If you just, you know, have some stuff that you want to poke around with. But if you are looking for more of an enterprise-ish monitoring tool, then you would get the new Darling version. It is a headless window service connected to a Postgres and timescale backend. Doing that helps to keep things free.
You know, I got asked why I won’t use SQL Server as a backend, and it’s because I don’t want to cost people money. Sorry. This thing being free, I can’t say it’s free and then make you pay for SQL Server licensing. It can either make you steal from poor beleaguered Microsoft using Developer Edition for your monitoring tool.
But it runs 24-7 agentless, collects stuff from all your SQL servers. I’ve got a couple client sites with around 150-200 servers being collected on a single VM, and it’s handling all that well. I had to make some adjustments to some of the compression stuff in there to keep things running smoothly.
But all is looking absolutely, well, I’d say Gucci, but Gucci is just cheap crap these days, right? We don’t care for cheap crap around here. But our databases are feeling the end of summer.
I think, is that corn whiskey or Everclear or something? I don’t know. But anyway, just to sort of give you an idea, just a brief idea where things are. This is the WPF. This is the sort of executable viewer.
You can use this with Windows. And I’m going to talk more next week about the web viewer, because the web viewer for this is really cool and has a lot of neat stuff in it. You can build custom notebooks.
If you’ve ever used Datadog and you’ve made a notebook in Datadog that lets you graph and charts very specific things for specific servers or store procedures or queries or whatever, you can do that with my web viewer now, too. It’s pretty neat.
But, you know, just over here, stuff that I’ve added kind of recently, availability group monitoring. This is just a local Postgres instance that I have. It’s not doing a whole lot. There’s no load on it.
It’s just a sanity check and make sure everything’s working. But this is my monitoring tool, monitoring Postgres now. So good for me. And, of course, if you still just love SQL Server, maybe you don’t love when Windows Update kicks in and messes up your graphs. Thanks, Windows Update.
You’re a real, real sport. Then you can continue to monitor SQL Server performance for free with this as well. But we’ve got a lot of cool stuff in here. Anyway, let’s go talk about what I promised to talk about.
And I forgot to go back up to the top of this. So this video, again, part of the Learn T-SQL series, the full course is available for purchase down below if you want to learn a lot about T-SQL from, you know, someone who spends a lot of time writing T-SQL and writing T-SQL monitoring tools and stuff like that. I don’t know.
It might be a good idea. It might be a legitimate, reliable source of information. It might get fewer things wrong than the robots. It might even have the robots help tidy things up sometimes. I don’t know.
You know. I don’t want to get a tangent, but man. It’s been still not a fun time talking to the robots about performance. Still not a fun time.
They follow instructions well sometimes, but they say so many dumb things. Anyway, if you are finding yourself in need of working with time zones, there are some things that you are going to care about if you are working in a database that maybe doesn’t have any time zone information and you need to get it in there or something.
Or maybe you just started a new job where they cared about time zones and then your other jobs didn’t. So any, all kinds of crazy things can happen. But when you’re working with time zones, especially if you have dates that are not time zone oriented in any way, like for example, this one, this is just a date time too.
And getting used to the functions and how they work and behave can be a little tricky at first. It can be a little mind numbing. So if we run this query, what I want to show you is just we start off with a date time too that has no time zone affiliated with it.
And if we convert that to a date time offset, let me blow this up so it looks a little bit better, then it just gets assumed UTC, right? So like, you know, I’m here in New York City, so I’m in Eastern time. And so if it was 1430 Eastern time and I converted that and I said, hey, well, now you’re in a time zone.
SQL Server would be like, you’re UTC. And I’d be like, I don’t think that’s right. And so if you if you try to use switch offset, just converting, you know, a date time or date time to to a date time offset, then it will start subtracting hours incorrectly from it.
What you need to do is use to date time offset instead of just that so that you get the sort of correct time and time zone applied. Right. And then you can do the old double jump. You can say switch offset to date time offset and you can you can it’s sort of like doing at time zone twice, which we’ll talk about later.
But this will actually get you to the right, you know, just to UTC time, you know, because this is 1430 minus five, the correct one. Sorry, 1430 minus five, the correct one. And then we get that to UTC where it’s plus zero, but it’s 1930.
All right. And if you’re American, welcome to 24 hour clocks, I guess. I don’t know. Using switch offset correctly is, you know, you really do kind of have to start with something that already has a time zone attached to it.
Right. So if you look at these queries, right, and we run this stuff, we can see our original time is 1430. Right. And I guess Eastern ish time, I guess. Right now, that might be wrong, depending on daylight saving stuff.
Who cares right now? Videos are forever. Right. This could be any time of year. Right. I don’t know when you’re going to watch this. It’s eventually consistent.
But original time is 1430 and minus five hour from UTC time zone. And then we can get to Pacific time, which is minus eight, which just subtracts the correct time there. And then we can go to UTC and we can even time travel all the way to Tokyo, where it is tomorrow.
Right. We go from 115 to 116 because Tokyo is so far ahead. Right. And all of this stuff ends up, we just do this same instant check between these things just to give us just to make sure that we get everything correct. To date time offset is an interesting one.
And you kind of have to look at why where you attach stuff matters a bit. Right. So if we run this set of queries, right, we can we can not only so go back a little bit here. In this set, we use a mix of things.
Right. We can you can either do like you can either do a string like this where you tell it how many like what the the the hour offset is or you can even use this to do like minutes. Right. So four 80 zero. You can add 540 minutes.
Right. Subtract 480 minutes. Go to UTC by adding zero. So you can do, you know, like do the same thing just, you know, with with numbers across the board. Right. So it doesn’t matter how you do the attach.
It just like what matters the most is that you just get the numbers right. You know, some people will store and we’ll talk about this a little bit, too, is some people will store like the name of the time zone, which is probably the smartest thing to do, because then you can look up what the current offset is and you don’t have to keep track of it.
Right. It’s like daylight savings and all that stuff gets real messy. If you just have a table with like some initial offset, you have to go and change it. You don’t know what the time you don’t know what the actual time zone is like for, you know, for let’s just say Eastern Standard Time.
There are a bunch of other time zone names that have the same offset as Eastern Standard Time, just storing the offset. You don’t know exactly which of the time zones it is. You just know the offset that might be good enough for you.
But usually you want to know. And this is usually what people do when they need to get something right. So we’re going to look at the timeline here where we have learned T-SQL.
We have started drinking because we were on a really long trip. And then we have forgotten T-SQL. And if we run all this stuff, see that this is a pretty good, let’s just call this canonical way to figure out what time things were originally logged.
Maybe, you know, what the original log time zone was. And then, you know, usually you want to, usually you would, so usually you store like, if you’re smart and you’re using time zones, you store everything in UTC, you store the user’s time zone, you do the lookup, and then you come back and you figure out what the offset is, like what the hour difference is there, right?
But that is what this sort of brings up here. The one thing, actually I’ll talk about that thing tomorrow, but let’s say that we have a table of sales orders here, right? And we have, let’s say, two options, right?
You can say what city someone is in and their current UTC offset, or you can store the name of their time zone, which I know strings in a database were a mistake. I agree usually, but, you know, what can we do here? But when you want to do a query that, or rather when you want to query this data, having the name of the time zone stored is much, much smarter, right?
Because at certain points, you might be wrong by an hour because you are not keeping track of time changes, right? Time zones are one part of the problem, clock changes are another part. And so you might be wrong, I should go this way, by some number of minutes sometimes if you are only storing the offset and not the actual time zone name, right?
So if we, you know, use the time zone name here, then we can, like, this will just screw you up, right? It’s not a good time because we’re just using offset minutes, but if we are storing the time zone name, then we can, you know, we can send a time to the time zone name, right?
Even just stored in a column like this, and then we can send it to UTC, and then we can look at the wrong by minutes column that just shows us how, if you’re only storing the offsets, you can be very wrong as clocks adjust throughout the year. And, of course, what was that comment? Ah, there we are.
At time zone returns date time offset, so switch offset works on the result too. These two do the same thing. Pick whichever reads better to you, right? So we could also just do something like this, right?
Switch offset with the time zone name, and we get back presumably all correct results, right? So we can do at time zone, or we could do switch offset. Both of these end up being correct.
You know, whatever query form you like the best, do that. Just as I said in previous videos, don’t do this in a where clause, because that makes Windows API calls, and that’ll really be bad for you.
You won’t have fun with it. All right. That’s enough about time zones. Thank you for watching.
Hope you enjoyed yourselves. I hope you learned something. I hope you will pretty please with free cherries on top. Check out my SQL Server performance monitoring tool.
It’s absolutely free. It’s available on GitHub. Links and stuff are down in the video description. If you want to monitor a SQL Server, availability groups, Postgres, or a Postgres, it’s all here.
It’s all free. Anyway, I’m out of here. I will see you next Tuesday for Office Hours. All right. Thank you for watching. Thank you.
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.